Simple count question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple count question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-05-14 : 09:44:58

I know this is simple but my minds gone blank

id desc
12345 Item 1
12345 Item 2
12346 Item 3
12346 Item 4
12347 Item 5

I want to count all the distinct ID's, so for the results here I need

id count
12345 2
12346 2
12347 1

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-05-14 : 09:49:50

select id ,count(*) from table group by id

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-05-14 : 09:55:04

Thanks,

Just taking it one step further I tried this

SELECT CapID, COUNT(*) AS c
FROM tblNewMatrixLombard
WHERE (c > 1)
GROUP BY CapID

It says invalid column name c - can I fix this?

Thanks

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-05-14 : 11:04:11

that's what HAVING is for. WHERE affects the data before it is grouped.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-05-14 : 11:43:10

Am sorry,

SELECT CapID, COUNT(*) AS c
FROM tblNewMatrixLombard
having (COUNT(*) > 1)
GROUP BY CapID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 11:53:17

quote:
Originally posted by sakets_2000

Am sorry,

SELECT CapID, COUNT(*) AS c
FROM tblNewMatrixLombard
~~having (COUNT(*) > 1)~~
GROUP BY CapID
having (COUNT(*) > 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources