wood burning stoves 2.0*
The moose likes JDBC and the fly likes subtotal using group by clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "subtotal using group by clause" Watch "subtotal using group by clause" New topic
Author

subtotal using group by clause

sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 179
Hi,
1.COUNTRY
---------
COUNTRY_ID
COUNTRY_NAME


2. STATE
---------
COUNTRY_ID
STATE_iD
STATE_NAME


3. DISTRICT
--------
STATE_ID
DISTRICT_ID
DISTRICT_NAME


4. CITY
--------
DISTRICT_ID
CITY_ID
CITY_NAME
POPULATION


I want to calcualte the avg population for the states in the country . In addition i would like to see the subtotal for State and Country.


select COUNTRY_NAME,STATE_NAME,avg(d.population)
FROM COuntry a,State b,District c,City d
where a.COUNTRY_ID=b.COUNTRY_ID
and b.STATE_iD =c.STATE_iD
and c.DISTRICT_ID=d.DISTRICT_ID
group by COUNTRY_NAME,STATE_NAME


regards
sudha
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Which database are you using, for oracle I know that functtion rollup & cube can solve your problem.

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 179
Shailesh,
I read regarding cube and rollup but i wasnt clear what is the difference between the two. can you please explain in detail.
regards
sudha
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

  • CUBE
  • This is the most generalized aggregation clause. The general syntax is CUBE ( ). It is used with the GROUP BY only. CUBE creates a subtotal of all possible combinations of the set of column in its argument. Once we compute a CUBE on a set of dimension, we can get answer to all possible aggregation questions on those dimensions.

    GROUP BY CUBE( a, b, c) is equivalent to

    GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).

  • ROLLUP

  • ROLLUP clause is used with GROUP BY to compute the aggregate at the hierarchy levels of a dimension.

    ROLLUP(a, b, c) assumes that the hierarchy is "a" drilling down to "b" drilling down to "c".

    ROLLUP (a, b, c) is equivalent to GROUPING SETS ( (a, b, c), (a, b), (a), ( )).


    your problem is little tricky because you want to use two aggregate functions (sum & avg)in a single query therefore you may have to go for inner query.

    you still didn't answer which database are you using ?

    Shailesh
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: subtotal using group by clause