CODEWITHSUNDEEP
mysqlsqljoin
user3904830
user3904830

Reputation:

Condition on Sum when using inner join?

I have two tables :

a
idA User Size 
b
idB status name

User is a foreign key from a to b.

This query worked well:

SELECT a.User,a.size,b.status,b.name, SUM(a.size) as TotalSize
FROM a
LEFT OUTER JOIN b
ON a.User=b.idB
WHERE a.size> 10
GROUP BY User
ORDER BY TotalSize DESC

but when I tried to PUT A condition on the sum it fails:

SELECT a.User,a.size,b.status,b.name, SUM(a.size) as TotalSize
FROM a
LEFT OUTER JOIN b
ON a.User=b.idB
WHERE TotalSize > 10  # or WHERE SUM(a.size)  > 10
GROUP BY User
ORDER BY TotalSize DESC

What should I do?

Upvotes: 1

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Use a having clause (which goes after the group by):

HAVING TotalSize > 10  # or HAVING SUM(a.size)  > 10

This is the right way to filter the results from an aggregation function.

Upvotes: 1

Related Questions