How to use aggregation functions in a WHERE?

Question

I’m doing a college paper on databases, and one of the Query what I must do is this:

• Show all creditors who had a larger purchase total than that R$ 5000,00;

I tried to:

SELECT NomeCredor, SUM(Compras.Valor)

FROM Credores, Compras

WHERE (idCredores = Compras.Credores_idCredores)

AND (SUM(Compras.Valor)) > 5000

GROUP BY NomeCredor

ORDER BY NomeCredor;

The problem is that the SUM(Compras.Valor) cannot be passed as a condition of WHERE. Does anyone know an alternative?

Answer 1

Try using the HAVING clause after Group By, much like WHERE, but check the condition within the grouping.

Excellent reference on HAVING in Sqlite.