How to display for which companies the amount for all orders is more than 500?

So displays the total amount for all firms:

SELECT SUM(zakaz.price) AS "Π‘ΡƒΠΌΠΌΠ° Π·Π°ΠΊΠ°Π·Π°", firma.fname AS "НазваниС Ρ„ΠΈΡ€ΠΌΡ‹", COUNT(zakaz.codf) AS "ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ Π·Π°ΠΊΠ°Π·ΠΎΠ²" FROM firma INNER JOIN zakaz ON zakaz.codf = firma.codf GROUP BY firma.fname

I try to do this, but it gives an error:

 SELECT SUM(zakaz.price) AS "Π‘ΡƒΠΌΠΌΠ° Π·Π°ΠΊΠ°Π·Π°", firma.fname AS "НазваниС Ρ„ΠΈΡ€ΠΌΡ‹", COUNT(zakaz.codf) AS "ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ Π·Π°ΠΊΠ°Π·ΠΎΠ²" FROM firma INNER JOIN zakaz ON zakaz.codf = firma.codf WHERE SUM(zakaz.price) > 500 GROUP BY firma.fname

Mistake:

The aggregate expression cannot be used in the WHERE clause unless it is contained in a subquery of the HAVING clause or in the select list, and the column being aggregated is not an external reference.

    1 answer 1

    It is necessary so:

     SELECT SUM(zakaz.price) AS "Π‘ΡƒΠΌΠΌΠ° Π·Π°ΠΊΠ°Π·Π°", firma.fname AS "НазваниС Ρ„ΠΈΡ€ΠΌΡ‹", COUNT(zakaz.codf) AS "ΠšΠΎΠ»ΠΈΡ‡Π΅ΡΡ‚Π²ΠΎ Π·Π°ΠΊΠ°Π·ΠΎΠ²" FROM firma INNER JOIN zakaz ON zakaz.codf = firma.codf GROUP BY firma.fname HAVING SUM(zakaz.price) > 500
    • Thank you, it works) - user248223
    • You are welcome. Not relevant to the question, but it is not recommended to use two languages ​​in the data model, or zakaz.cena, or order.price. - 0xdb
    • @ 0xdb, in the task such variables are specified. Can I have another question? - user248223
    • The task is composed with a non-critical error. Which one - 0xdb
    • @ 0xdb, you probably need to throw off the code. I will add in the question. In general, I derive a list of data from three tables. But you need to withdraw only those who belong to all groups. Tried to use HAVING as in this example, but not successfully - user248223