smantz
asked on
Counting a particular number of instances of a record
I am trying to create a query that allows me find individuals who have two or more instances of a particular record for them where the field value =1234.
Any ideas and I may need to elaborate if this is not clear? MS Sql 2000 is the DB
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select
individual,
sum(case when fieldvalue = 1234 then 1 else end) AS [1234_count]
--, sum(case when fieldvalue is null or fieldvalue <> 1234 then 1 else end) AS [other_count]
from
table_name
group by individual
having sum(case when fieldvalue = 1234 then 1 else end) > 1
individual,
sum(case when fieldvalue = 1234 then 1 else end) AS [1234_count]
--, sum(case when fieldvalue is null or fieldvalue <> 1234 then 1 else end) AS [other_count]
from
table_name
group by individual
having sum(case when fieldvalue = 1234 then 1 else end) > 1
ASKER
Thanks