How to get count of records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get count of records

Author

Topic

Rich75
Starting Member

8 Posts

Posted - 2013-03-27 : 11:49:18

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-27 : 11:54:05

[code]SELECT
Main_Number,
COUNT(DISTINCT Sub_Number) as Count_of_Distinct_Sub_Numbers
FROM
YourTableNameHere
GROUP BY
Main_Number;[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-27 : 11:55:49

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.

Rich75
Starting Member

8 Posts

Posted - 2013-03-27 : 11:57:32

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 !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources