CODEWITHSUNDEEP
sqlcountsubquery
Joe
Joe

Reputation: 23

SQL Query Help: Returning distinct values from Count subquery

I've been stuck for quite a while now trying to get this query to work. Here's the setup:

I have a [Notes] table that contains a nonunique (Number) column and a nonunique (Result) column. I'm looking to create a SELECT statement that will display each distinct (Number) value where the count of the {(Number), (Result)} tuple where Result = 'NA' is > 25.

Number | Result  
 100   |  'NA'  
 100   |  'TT'  
 101   |  'NA'  
 102   |  'AM'  
 100   |  'TT'  
 200   |  'NA'  
 200   |  'NA'  
 201   |  'NA'

Basically, have an autodialer that calls a number and returns a code depending on the results of the call. We want to ignore numbers that have had an 'NA'(no answer) code returned more than 25 times.

My basic attempts so far have been similar to:

SELECT DISTINCT n1.Number 
FROM Notes n1 
WHERE (SELECT COUNT(*) FROM Notes n2
       WHERE n1.Number = n2.Number and n1.Result = 'NA') > 25

I know this query isn't correct, but in general I'm not sure how to relate the DISTINCT n1.Number from the initial select to the Number used in the subquery COUNT. Most examples I see aren't actually doing this by adding a condition to the COUNT returned. I haven't had to touch too much SQL in the past half decade, so I'm quite rusty.

Upvotes: 2

Views: 3703

Answers (2)

Andrew Cooper
Andrew Cooper

Reputation: 32576

Try this:

SELECT Number
FROM (
    SELECT Number, Count(Result) as CountNA 
    FROM Notes
    WHERE Result = 'NA'
    GROUP BY Number
)
WHERE CountNA > 25

EDIT: depending on SQL product, you may need to give the derived table a table correlation name e.g. 

SELECT DT1.Number
  FROM (
        SELECT Number, Count(Result) as CountNA 
          FROM Notes
         WHERE Result = 'NA'
         GROUP 
            BY Number
       ) AS DT1 (Number, CountNA)
 WHERE DT1.CountNA > 25;

Upvotes: 2

Arnaud F.
Arnaud F.

Reputation: 8452

you can do it like this :

SELECT Number
FROM Notes 
WHERE Result = 'NA'
GROUP BY Number
HAVING COUNT(Result) > 25

Upvotes: 5

Related Questions