CODEWITHSUNDEEP
mysqlsqlsubquery
Pietro
Pietro

Reputation: 125

Use of subquery with count

I'm trying to count all the persons that postulated more than three times for a any job this year (2014). Grouping by month. One thing to have in mind is that the size of the table is 72 GB, so I'm trying to make the query as efficient as possible. I'm using the following query, but it didn't give me any results. Can someone tell me what is wrong, or what would be the best way to do it?

select month(postulationdate), count(idpostulant) from postulations
where postulationdate >= '2014-01-01'
and idpostulant = (select count(idpostulant) >= 3)
group by 1

If there's any question I'll glady answer it.

Upvotes: 0

Views: 45

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can use a HAVING clause to filter based on an aggregate:

SELECT month(postulationdate), count(idpostulant) 
FROM postulations
WHERE postulationdate >= '2014-01-01' 
GROUP BY month(postulationdate)
HAVING count(idpostulant) >= 3

If you have an index on postulationdate it will expedite the initial filtering that takes place in your WHERE clause.

Update: It occurs that you might mean >= 3 in 2014 total, then broken up by month, you can do this:

SELECT month(postulationdate), count(idpostulant) 
FROM postulations AS a
WHERE postulationdate >= '2014-01-01' 
  AND EXISTS(SELECT idpostulant 
             FROM postulations AS b
             WHERE postulationdate >= '2014-01-01' 
                AND a.idpostulent = b.idpostulent
             GROUP BY idpostulant
             HAVING count(idpostulant) >= 3)                    
 GROUP BY month(postulationdate)

Upvotes: 1

Related Questions