sql - 基于国家值(value)的汇总

Question

我在 Oracle 10g 中有下表

Country    State/city          No. of males        No. of females
US          TN                  500000                 450000
US          CA                  200000                 180000
US          GA                  600000                 650000
US          KY                  100000                  75000
England  Manchester             900000                 750000
England  London                1000000                 950000
England  Birmingham             250000                 200000
India    Delhi                 1000000                1000000
India    Mumbai                1000000                 900000
India    Chennai                750000                 500000

我想要如下所示的输出。

Country    State/city          No. of males        No. of females
US          TN                  500000                 450000
US          CA                  200000                 180000
US          GA                  600000                 650000
US          KY                  100000                  75000
Total                          1400000                1370000
England  Manchester             900000                 750000
England  London                1000000                 950000
England  Birmingham             250000                 200000
Total                          2150000                1500000
India    Delhi                 1000000                1000000
India    Mumbai                1000000                 900000
India    Chennai                750000                 500000
Total                          2750000               19500000

我尝试使用 ROLLUP 进行输出，但在所有行的末尾只得到总计。我想计算每个国家的总数。

Answer 1

这可以通过 GROUP BY GROUPING SETS 来完成 表达式:

SELECT 
    country, 
    state, 
    SUM(males) AS males, 
    SUM(females) AS females
FROM 
    stats
GROUP BY GROUPING SETS
    ( (country, state),
      (country),
      ()                         -- bonus: a Grand Total
    ) ;

测试于 SQL-Fiddle