counting number of id's in SQL through Case

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)

counting number of id's in SQL through Case

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-10-02 : 11:37:30

here is my code

select id, count(const) as constituent
from gifts
group by const

Output sample
ID const
2232 c
2232 a
2122 b
2526 v

lets say I want to count the number of occurances within the id field
Would I do a select case when ID >1 then 1 else 0 end as idCount. In this scenario, I only want to show the following
ID const
2232 c
2232 a
not the remaining ones

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-02 : 11:47:14

How is it possible to select id when grouping by const?
I think your sample is not useful.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:51:49

didnt you understand solution provided earlier?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133824

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-02 : 13:17:14

[code]-- 1. Get all non-distinct IDs:

SELECT ID
FROM Gifts
GROUP BY ID
HAVING COUNT(*) > 1

-- 2. Use that Query to get the data you want:

SELECT
ID,
Const
FROM
Gifts
INNER JOIN
(
SELECT ID
FROM Gifts
GROUP BY ID
HAVING COUNT(*) > 1
) AS T
ON Gifts.ID = T.ID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources