Running mySQL Statement results in nothing returned

Question

So I am doing homework for mysql class and I am having a hard time understanding why my code is bringing back no data, it runs but returns nothing when I know that data that meets the criteria exists. Please help. Here is my code:

SELECT vendor_id
     , SUM(invoice_total) AS invoice_gt
     , AVG(invoice_total) AS invoice_avg
     , COUNT(invoice_id) AS invoice_qty
  FROM invoices
 WHERE "invoice_avg" > 300
 GROUP 
    BY vendor_id
 ORDER 
    BY "invoice_avg" DESC

Yes, if I remove the WHERE "invoice_avg" > 300 I return 34 rows with data that includes values that are greater than 300 in the invoice_avg column — mildlylost, Sep 28 '19 at 19:37
`"invoice_avg"` is a string. It won't ever be greater than 300. Try removing the quotes (or use backticks as needed instead of double quotes). See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql for more. — L. Scott Johnson, Sep 28 '19 at 19:39
Note that depending on your settings for [ANSI_QUOTES](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi_quotes), `"invoice_avg"` will be treated as a constant string or column name. However - If `ANSI_QUOTES` were enabled, you should get an error message for this query. — Paul Spiegel, Sep 29 '19 at 15:11

score 0 · Answer 1 · answered Sep 28 '19 at 19:42

0

Remove quotes and try

USE ap;
SELECT vendor_id, SUM(invoice_total) AS invoice_gt, AVG(invoice_total) AS 
invoice_avg, COUNT(invoice_id) AS invoice_qty
FROM invoices
WHERE invoice_avg > 300
GROUP BY vendor_id
ORDER BY invoice_avg DESC

answered Sep 28 '19 at 19:42

Dhanush

112
7

It says that there is "Unknown column 'invoice_avg' in 'where' clause – mildlylost Sep 28 '19 at 19:46

score 0 · Accepted Answer · answered Sep 28 '19 at 19:42

0

You can't use an aggregated column in the WHERE clause.
Use it in a HAVING clause which is evaluated after the SELECT statement:

SELECT vendor_id, SUM(invoice_total) AS invoice_gt, AVG(invoice_total) AS invoice_avg, COUNT(invoice_id) AS invoice_qty
FROM invoices
GROUP BY vendor_id
HAVING invoice_avg > 300
ORDER BY invoice_avg DESC

answered Sep 28 '19 at 19:42

forpas

160,666
10
38
76

1

Thank you, marked it new to using the platform besides google searches so I appreciate the heads up – mildlylost Sep 28 '19 at 19:51

Running mySQL Statement results in nothing returned

2 Answers2