Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: SQL scripting help

    Hello, I just started learning SQL today and have come across a problem. The problem I am trying to script is this:

    "List order number, price, quantity and order price * quantity for all order-lines. Use the column alias EXTEND_PRICE for price* quantity. The list should be in order of order_number."

    The problem is that ORDER_NUMBER has duplicate values that need to be added up for the total in EXTENDED_PRICE. When i try to use the "GROUP_BY" command I get all kind of errors(the errors say that they cant list ORDER_PRICE and QUANTITY because they arent in the GROUP BY field even though it seems to work in scripts when I dont use the alias comand "AS"). This is the coding I have so far:

    SELECT ORDER_NUMBER, ORDER_PRICE, QUANTITY, (ORDER_PRICE*QUANTITY)AS EXTENDED_PRICE
    FROM ORDERLINE
    GROUP BY ORDER_NUMBER
    ORDER BY ORDER_NUMBER


    The other problem is this:

    "LIst the ORDER_NUMBER and total of ORDER_PRICE * QUANTITY for all order-lineswhere the total of ORDER_PRICE * QUANTITY is greater than 60. Put the list in descending order of ORDER_PRICE * QUANTITY"


    Pretty much the same problem as before, cant get the group by command to work whem using the "AS" function. Maybe im doing it all wrong, please help!

    "SELECT ORDER_NUMBER, (ORDER_PRICE*QUANTITY)AS EXTENDED_PRICE
    FROM ORDERLINE
    WHERE EXTENDED_PRICE>60
    GROUP BY ORDER_NUMBER
    ORDER BY EXTENDED_PRICE DESC"


    Thanks for taking the time to look at all of this guys, I really appreciate it.

  2. #2
    Join Date
    Oct 2003
    Location
    Sweden
    Posts
    45

    Re: SQL scripting help

    Hi Osiris,

    You need to use some aggregate function on all columns that are not in the group by clause. Like this

    Code:
    SELECT ORDER_NUMBER, SUM(ORDER_PRICE), SUM(QUANTITY), SUM(ORDER_PRICE * QUANTITY) AS EXTENDED_PRICE
            FROM ORDERLINE 
            GROUP BY ORDER_NUMBER
            ORDER BY ORDER_NUMBER
    Hope this helps.

    Robert

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    Thank you so much for helping me out, it was getting so frustrating trying to figure out what syntax it wanted all by myslef. I sure am glad that I am paying for a class that doesnt teach me how to do things.
    Once again thanks tons.

    Marshall

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •