SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get count of records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rich75
Starting Member

8 Posts

Posted - 03/27/2013 :  11:49:18  Show Profile  Reply with Quote

I have the following data:

Main_Number Sub_Number
13965015 13965016
13965015 13965017
13965028 13965029
13965028 13965029
13965033 13965034
13965033 13965034

I want to display a count of distinct Sub Numbers for each Main Number. So my results would look like this:

Main_Number Count_of_Distinct_Sub_Numbers
13965015 2
13965028 1
13965033 1

I'm not sure where to begin in writing this query. Could you offer some advice.

Thanks

James K
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/27/2013 :  11:54:05  Show Profile  Reply with Quote
SELECT
   Main_Number,
   COUNT(DISTINCT Sub_Number) as Count_of_Distinct_Sub_Numbers 
FROM
   YourTableNameHere
GROUP BY
   Main_Number;
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 03/27/2013 :  11:55:49  Show Profile  Visit webfred's Homepage  Reply with Quote
This?

select Main_number, count(distinct sub_number) as cnt from table group by Main_number


Too old to Rock'n'Roll too young to die.


Edited by - webfred on 03/27/2013 11:56:21
Go to Top of Page

Rich75
Starting Member

8 Posts

Posted - 03/27/2013 :  11:57:32  Show Profile  Reply with Quote
quote:
Originally posted by James K

SELECT
   Main_Number,
   COUNT(DISTINCT Sub_Number) as Count_of_Distinct_Sub_Numbers 
FROM
   YourTableNameHere
GROUP BY
   Main_Number;




Thank You !
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000