locked
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.

    Answer:

    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?

    Thanks

    Monday, July 29, 2013 11:14 AM

Answers

All replies

  • no it should be 

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

    having is used along with an aggregate function 

    http://www.programmerinterview.com/index.php/database-sql/having-vs-where-clause/

    http://beginner-sql-tutorial.com/sql-having-clause.htm

    vt


    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