-1
SELECT Product_Finish AS "Desk Finish",    
  AVG(Standard_Price) AS "Average Price"
FROM Product_T
WHERE "Average Price">300
ORDER BY "Average Price" DESC;

What did I do wrong?

GMB
  • 216,147
  • 25
  • 84
  • 135
Ryan Mehdian
  • 1
  • 2
  • 1
    Does this answer your question? [What's the difference between HAVING and WHERE?](https://stackoverflow.com/questions/287474/whats-the-difference-between-having-and-where) – Mike Doe Mar 15 '20 at 22:06
  • When quoting names in MySQL you have to use the backtick `\``, also you cant `AVG()` without specifying a `GROUP` (or it will auto-group everything in one row), so you have to figure out what you want which is probably not that. – Havenard Mar 15 '20 at 22:20

1 Answers1

1

There are several issues:

  • you use aggregate function AVG() but the query has no GROUP BY clause

  • the where clause refers to an aggregated column; you must use a having clause for this

  • identifiers are surrounded with double quotes; MySQL wants backticks instead

So:

SELECT Product_Finish AS `Desk Finish`, AVG(Standard_Price) AS `Average Price`
FROM Product_T
GROUP BY Product_Finish
HAVING `Average Price`> 300
ORDER BY `Average Price` DESC;

Better yet would be to use identifiers that require no quoting. Also, it is worth noting that, unlike MySQL, not all databases support column aliases in the having clause (in which case you need to repeat the aggregate expression). This is standard SQL:

SELECT Product_Finish AS desk_finish, AVG(Standard_Price) AS average_price
FROM Product_T
GROUP BY Product_Finish
HAVING AVG(Standard_Price) > 300
ORDER BY average_price DESC;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    If you can provide a minimal example of your data and what you expect to extract from it, it will be easier to provide you with an useful answer. Right now we are just guessing here. – Havenard Mar 15 '20 at 22:25
  • @RyanMehdian: please provide the full error message. Just the error number is not enough. – GMB Mar 15 '20 at 22:28
  • Write a query to show the average price for each finish available in desk products where the average price is greater than $300. Show the finish under the heading "Desk Finish" and the average price under the heading "Average Price". Order the results by decreasing average price. – Ryan Mehdian Mar 15 '20 at 22:58
  • Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'cis_3050_pvfc.Product_T.ProductFinish'; this is incompatible with sql_mode=only_full_group_by – Ryan Mehdian Mar 15 '20 at 23:01
  • @RyanMehdian: please run the query exactly as provided. Both above queries do have a `GROUP BY` clause. – GMB Mar 15 '20 at 23:31