CODEWITHSUNDEEP
mysqlsql
Jordan
Jordan

Reputation: 13

Use value set by count in where statement

I have a query where I'm selecting the number of users that have submitted to each application.

SELECT c.*, count(p.id) people
FROM users p
LEFT JOIN apps c
ON c.id = p.app
WHERE c.status='1'
GROUP BY p.app
ORDER BY c.id DESC

However, I only want to select the ones that have less than 50 people. How can I do this?

I tried WHERE c.status='1' AND people < 50 but it didn't work it said it's an invalid column. How can I only select the values where people is less than 50?

Thanks

Upvotes: 1

Views: 68

Answers (1)

John Woo
John Woo

Reputation: 263703

use HAVING. It behaves like WHERE but its use is for aggregated columns.

SELECT c.*, count(p.id) people
FROM users p
LEFT JOIN apps c
ON c.id = p.app
WHERE c.status='1'
GROUP BY p.app
HAVING count(p.id) < 50
ORDER BY c.id DESC

Upvotes: 2

Related Questions