Rollup based on country value

Question

I have the following table in 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

I would like to have a output like the one shown below.

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

I tried output using ROLLUP but I get only Grand total at the end of all the rows. I would like to calculate total for each country.

Answer 1

This can be done with GROUP BY GROUPING SETS expression:

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

Tested at SQL-Fiddle