SQL Query Group By RSS

• • Reply
  

  2177

  Member

  375 Points

  325 Posts

  SQL Query Group By

  Mar 07, 2012 04:35 PM|LINK

  I have following table in SQL Server

  CountryID   StateID   CityID
  1                2            1
  1                2            1
  1                2            2
  1                2            2
  1                2            3
  1                3            4
  1                3            4
  1                3            5
  ...

  And I need below output

  CountryID StateID  Total City
  1              2           3
  1              3           2
  ...

  Could you please tell me the required SQL for this?

  Thanks & Regards,
  SP
  Please do "Mark As Answer" if this helps you.
• • Reply
  

  adamturner34

  Contributor

  3964 Points

  999 Posts

  Re: SQL Query Group By

  Mar 07, 2012 04:48 PM|LINK

  Select CountryID, StateID, COUNT(*)[Total City]

  from myTable

  group by CountryID, StateID, CityID

  • Edited by adamturner34 on Mar 07, 2012 04:49 PM
• • Reply
  

  parag.pratap...

  Member

  116 Points

  35 Posts

  Re: SQL Query Group By

  Mar 07, 2012 04:51 PM|LINK

  SELECT CountryID ,StateID  , COUNT(CityID) TotalCity FROM xyz GROUP BY CountryID ,StateID ,CityID;

  Please mark as Answer if post helps in resolving your issue
  
• • Reply
  

  Vipindas

  Contributor

  5514 Points

  810 Posts

  Re: SQL Query Group By

  Mar 07, 2012 04:55 PM|LINK

  SELECT [CountryID]
        ,[StateID]
        ,Count([CityID]) As TotalCity
    FROM [Test]
    Group By [CountryID],[StateID]

  You will get

  CountryID   StateID     TotalCity
  ----------- ----------- -----------
      1               2           5
      1               3           3

  Please remember to mark as answer if it helps you
• • Reply
  

  2177

  Member

  375 Points

  325 Posts

  Re: SQL Query Group By

  Mar 07, 2012 05:02 PM|LINK

  Ya but I need unique count.

  Please see my expected output in my original question. Thanks.

  Thanks & Regards,
  SP
  Please do "Mark As Answer" if this helps you.
• • Reply
  

  2177

  Member

  375 Points

  325 Posts

  Re: SQL Query Group By

  Mar 07, 2012 05:20 PM|LINK

  I figured it out.

  SELECT [CountryID] 
        ,[StateID] 
        ,Count(DISTINCT [CityID]) As TotalCity 
    FROM [Test] 
    Group By [CountryID],[StateID]

  Thanks & Regards,
  SP
  Please do "Mark As Answer" if this helps you.
  • Marked as answer by 2177 on Mar 07, 2012 05:20 PM