What is wrong with this SQL command? RRS feed

  • Question

  • Question: Write an SQL command using the 'having' clause that retrieves the order numbers of orders over $1000 grouped by the date ordered from the OrderDetails table.


    SELECT OrderRef FROM OrderDetails

    ORDER BY DateOrdered

    HAVING VALUE>1000;

    When I test the above it doesn't work, can someone please let me know what I am doing wrong?


    Monday, July 29, 2013 11:14 AM


All replies

  • no it should be 

    SELECT OrderRef FROM OrderDetails
    ORDER BY DateOrdered
    where [VALUE]>1000;

    having is used along with an aggregate function 




    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Edited by SimpleSQL Monday, July 29, 2013 11:19 AM edit
    • Proposed as answer by Olaf HelperMVP Monday, July 29, 2013 11:28 AM
    • Marked as answer by Allen Li - MSFT Sunday, August 4, 2013 12:24 PM
    Monday, July 29, 2013 11:18 AM
  • Hello,

    You can use HAVING only together with a GROUP BY and an aggregation e.g. SUM or COUNT.

    Olaf Helper

    Blog Xing

    Monday, July 29, 2013 11:28 AM
  • Your query should look like as below. I am using SUM aggregate function. You can use COUNT,AVG.... base on your need.

    SELECT OrderRef,SUM([Value]) [Value] FROM OrderDetails
    GROUP by OrderRef
    HAVING SUM([Value]) > 1000;
    ORDER BY DateOrdered

    Regards, RSingh

    Monday, July 29, 2013 11:30 AM
  • Either v.vt's or RSingh()'s reply is what you want, depending on what you are trying to achieve (although in v.vt's example I would switch the WHERE clause and the ORDER BY clause). Do you want to filer out all rows with [Value] <= 1000 or do you want to aggregate [Value] and filter out all rows with an aggregated value <= 1000? Or maybe you want to filter out all rows with [Value] <= 1000 and then use some aggregate?

    • Edited by Chris Sijtsma Monday, July 29, 2013 12:08 PM grammar error
    Monday, July 29, 2013 12:06 PM