Win a copy of Kotlin in Action this week in the Kotlin forum!
programming forums Java Java JSRs Mobile Certification Databases Caching Books Engineering Languages Frameworks Products This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
Sheriffs:
Saloon Keepers:
Bartenders:

# subtotal using group by clause

sudha swami
Ranch Hand
Posts: 186
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
Posts: 1084
Which database are you using, for oracle I know that functtion rollup & cube can solve your problem.

Shailesh

sudha swami
Ranch Hand
Posts: 186
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
Posts: 1084
• 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