Condition based on COUNT(*) in query with GROUP BY clause

Question

I'm given a table Employee with 300,000 records that contains the following fields:

Query should return location_id values that have more than 75000 records with their count.

so, what have you tried? We're not here to write your code for you. — serakfalcon, Sep 01 '14 at 14:03
possible duplicate of [Aggregate function in SQL WHERE-Clause](http://stackoverflow.com/questions/6319183/aggregate-function-in-sql-where-clause) — Bulat, Sep 01 '14 at 14:25

score 5 · Accepted Answer · edited Sep 01 '14 at 14:15

5

This is the query you're looking for:

SELECT T.Location_Id
     ,COUNT(T.Location_Id) AS [nbRecords]
FROM yourTable T
GROUP BY T.Location_Id
HAVING COUNT(T.Location_Id) > 75000

Hope this will help you.

edited Sep 01 '14 at 14:15

Mutton92

answered Sep 01 '14 at 13:54

Joël Salamin

and If I want to show the records with exactly 0(zero) count? – Jatin Sehgal Sep 16 '14 at 13:50
@JatinSehgal You just have to change the `HAVING` clause as following: `HAVING COUNT(T.Location_Id) = 0` – Joël Salamin Sep 16 '14 at 14:09
@JatinSehgal My mistake, how do you want to show the location that doesn't exist in your table? (That the meaning of a count equal to zero!) – Joël Salamin Sep 16 '14 at 14:18
I just wanted to know whether such a scenario is possible where in I want to show the count of each of the possible values of column used in group by clause. Ex select count(OA.dateComplete) from OA join OAR on ...... where OA.dateComplete between '' AND '' group by Oa.dateComplete – Jatin Sehgal Sep 16 '14 at 14:23
@JatinSehgal Yes it's possible to count the number of occurrences as you want but you need to define a `LEFT JOIN` in order to keep every values from the main table even if there is no correspondance in the joined table (in this case you'll be able to filter on `COUNT(...) = 0`. – Joël Salamin Sep 16 '14 at 14:31

1 Answers1