Counting a particular number of instances of a record

Posted on 2011-10-31
Medium Priority
Last Modified: 2012-05-12

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
Question by:smantz
LVL 11

Accepted Solution

Simone B earned 2000 total points
ID: 37059323
Is this what you are looking for?

Select individual from tablename
where fieldvalue = 1234
group by individual
having count(individual) > 1

Author Comment

ID: 37059624
Worked great but needed to change last, "count (individual)" to Count(fieldvalue)
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37059786
    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]
group by individual
having sum(case when fieldvalue = 1234 then 1 else end) > 1

Featured Post

What Security Threats Are We Predicting for 2018?

Cryptocurrency, IoT botnets, MFA, and more! Hackers are already planning their next big attacks for 2018. Learn what you might face, and how to defend against it with our 2018 security predictions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question