CODEWITHSUNDEEP
sqloracle-databaseselectcount
user2444400
user2444400

Reputation: 139

SQL - How to display only certain values of the Count Aggregate Function?

what I'm trying to do is to only display only rows with a count value greater than 3. 

select pharm_name, count(1) as "Number of Staff"
from pharmacies p, pharmacy_staff_store pss
where p.pharm_id = pss.pharmacy_id
group by pharm_name;

For example this query might return me 5 rows where under the "Number of Staff" it'll say for instance 5,4,3,2,1 but I only want it to return me those rows where the count is 3 and above. Is there a feasible way to do this?

Upvotes: 1

Views: 3892

Answers (2)

SSD
SSD

Reputation: 369

use the having :

select pharm_name, count(1) as "Number of Staff"
from pharmacies p, pharmacy_staff_store pss
where p.pharm_id = pss.pharmacy_id    
group by pharm_name
having count(1) > 3

or you can write in this way:

select * from (
select pharm_name, count(1) as x
from pharmacies p, pharmacy_staff_store pss
where p.pharm_id = pss.pharmacy_id
group by pharm_name)
where x>3

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

First dont use WHERE join Promote the use of explict JOIN sintaxis, Aaron Bertrand wrote a nice article Bad habits to kick : using old-style JOINs about it.

Then use HAVING to filter from the result.

SELECT pharm_name, 
       Count(1) AS "Number of Staff" 
FROM   pharmacies p
JOIN   pharmacy_staff_store pss 
  ON   p.pharm_id = pss.pharmacy_id 
GROUP  BY pharm_name
HAVING COUNT(1) > 3;

Also I wouldnt use COUNT(1) if someone change the order of the fields on db your query wouldnt notice and will show wrong behavior. Use Count(fieldname)

Upvotes: 0

Related Questions