locked
Count at different levels RRS feed

  • Question

  • Hi Team,

    Please help in finding the count

    i have three levels Country,State,City i have to get the count of each of the column like India 10, Karanataka 5, Bangalore 4 (Count of people) and this count depend on the condition

    somebody suggest how to get this count!!

    Thanks,

    Eshwar.

    Thursday, July 29, 2010 10:21 AM

Answers

  • Eshwar,

    As demonstrated in the posts above, it is really easy to use ROLLUP or CUBE with GROUP BY for hierachical aggregations (this is what happens in an OLAP cube). But then the challenge begins.... How do you handle the rollup NULLS?

    Related link:

    http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/

     


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Marked as answer by KJian_ Wednesday, August 4, 2010 9:13 AM
    Wednesday, August 4, 2010 5:25 AM

All replies

  • Please post sample data (CREATE TABLE..INSERT INTO) for testing.Also always state what version you are using
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, July 29, 2010 10:32 AM
    Answerer
  • Hi,

    I suppose you have table containg demographic details and you want count of person for each state, city and country. If this is the case, then following will help

    If you are using SQL 2008 then you can create query as shwon below

    select id,city,country,state1,count(*) as countforcoomand
    from dbo.person
    group by grouping sets(id,city,country,state1 )
    having id is null

    if you are using 2005 then you can use CUBE syntex,

    select id,city,country,state1,count(*)
    from dbo.person
    group by id,city,country,state1 with cube

    Aprt from these gruping you can use the UNION ALL method as shown below

    http://technet.microsoft.com/en-us/library/bb510427.aspx

    Hope this helps. 


    -Chintak
    Thursday, July 29, 2010 11:10 AM
  • select Country,State,City,
    count(1)
    from Geography
    group by 
     RollUP (Country,State,City)
     order by Country
    

    hi,

    as i understood you need a roolup grouping. for example according to the code below, returns a resultset that includes total number of cities, and for the states and countries also.

    maybe i cant explain clearly the result but you can try it and see what will you get. hope it will help you.

    thanks.

    Thursday, July 29, 2010 11:26 AM
  • To get the result sorted according to the location hierarchy (detail followed by total) you can rely on the grouping() function, like so:

    -- SQL Server 2008

    select country, state, city, count(*) as cnt

    from yourtable

    group by rollup(country, state, city)

    order by

      grouping(country), country,

      grouping(state), state,

      grouping(city), city;

     

    -- Pre 2008

    select country, state, city, count(*) as cnt

    from yourtable

    group by country, state, city

    with rollup

    order by

      grouping(country), country,

      grouping(state), state,

      grouping(city), city;

     


    -- BG
    Thursday, July 29, 2010 11:40 AM
  • you can use count with over statement

    Count(ID) OVER (PARTITION BY city) ENDDATE

    you can replace city with country or what ever you want

    • Proposed as answer by Muhammad Abbas Thursday, July 29, 2010 12:08 PM
    Thursday, July 29, 2010 12:08 PM
  • CREATE TABLE tblPopulation (
    Country VARCHAR ( 100 ),
    [State] VARCHAR ( 100 ),
    City VARCHAR ( 100 ),
    [Population (in Millions)] INT
    )
    GO
    INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'East Delhi' , 9 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'South Delhi' , 8 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'North Delhi' , 5.5 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Delhi' , 'West Delhi' , 7.5 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Karnataka' , 'Bangalore' , 9.5 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Karnataka' , 'Belur' , 2.5 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Karnataka' , 'Manipal' , 1.5 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Maharastra' , 'Mumbai' , 30 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Maharastra' , 'Pune' , 20 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Maharastra' , 'Nagpur' , 11 )
    INSERT INTO tblPopulation VALUES ( 'India' , 'Maharastra' , 'Nashik' , 6.5 )
    GO

    SELECT Country , [State] , City ,
    SUM ( [Population (in Millions)] ) AS [Population (in Millions)]
    FROM tblPopulation
    GROUP BY Country , [State] , City WITH ROLLUP

    -- see this article http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/

    Thursday, July 29, 2010 12:30 PM
  • Eshwar,

    As demonstrated in the posts above, it is really easy to use ROLLUP or CUBE with GROUP BY for hierachical aggregations (this is what happens in an OLAP cube). But then the challenge begins.... How do you handle the rollup NULLS?

    Related link:

    http://www.sqlusa.com/bestpractices2005/subtotaltotalgrandtotal/

     


    Kalman Toth, SQL Server & Business Intelligence Training; BI TRIO 2008
    • Marked as answer by KJian_ Wednesday, August 4, 2010 9:13 AM
    Wednesday, August 4, 2010 5:25 AM