MySQL高级求和查询

Question

我有一个表，看起来像这样：

+--------------+------------+-------------+
| TaxAuthority | Effective  | AuthTaxRate |
+--------------+------------+-------------+
| city1        | 1993-01-01 |         1.0 |
| city1        | 1994-01-01 |         1.5 |
| city2        | 1993-09-01 |         1.5 |
| city2        | 1994-01-01 |         2.0 |
| city2        | 1995-01-01 |         2.5 |
| city3        | 1993-01-01 |         1.9 |
| city3        | 1993-07-01 |         2.3 |
| county1      | 1993-01-01 |         2.3 |
| county1      | 1994-10-01 |         2.5 |
| county1      | 1995-01-01 |         2.7 |
| county2      | 1993-01-01 |         2.4 |
| county2      | 1994-01-01 |         2.7 |
| county2      | 1995-01-01 |         2.8 |
| state1       | 1993-01-01 |         0.5 |
| state1       | 1994-01-01 |         0.8 |
| state1       | 1994-07-01 |         0.9 |
| state1       | 1994-10-01 |         1.1 |
+--------------+------------+-------------+

我正在尝试选择1994年11月1日city2的税率。示例：

City2 = 2.0
County1=2.5
State1=1.1
Total=5.6

到目前为止，我已经得到了所有的总数加起来是5.6；但是，我不知道如何将这3个和相加。这是我到目前为止所做的陈述：

    select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'City2' 
    and Effective <= '1994-11-01' 
    and Effective > '1993-09-01' 
    union 
    select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'County1' 
    and Effective <= '1994-11-01' 
    and Effective > '1993-09-01' 
    union 
    select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'State1' 
    and Effective <= '1994-11-01' 
    and Effective > '1994-07-01';

我的声明的结果是：

+-------------+
| AuthTaxRate |
+-------------+
|         2.0 |
|         2.5 |
|         1.1 |
+-------------+

有人知道如何将这些值相加吗？

Answer 1

我现在确定了，但你可以这样尝试-

Select sum( 
 select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'City2' 
    and Effective <= '1994-11-01' 
    and Effective > '1993-09-01' 
    union 
    select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'County1' 
    and Effective <= '1994-11-01' 
    and Effective > '1993-09-01' 
    union 
    select AuthTaxRate 
    from TaxRates 
    where TaxAuthority = 'State1' 
    and Effective <= '1994-11-01' 
    and Effective > '1994-07-01';
 ) As  total;