• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

subtotal using group by clause

 
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Which database are you using, for oracle I know that functtion rollup & cube can solve your problem.

Shailesh
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
  • 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
    reply
      Bookmark Topic Watch Topic
    • New Topic