ORDER BY the total of a GROUP BY

  • I have a table which contains order information. I need to SUM up the quantities ordered on an account every day. For example i could have the following records in a table

    Account Number QTY Name ROWID

    123456 4 Example 1

    123456 6 Example 2

    Now I have the following query which gets me the total of the quantities for the accountnumber

    SELECT AccountNo, SUM(CONVERT(INT,QTY))AS UOM

    FROM tfrOrderTransmissionDetailLog

    WHERE DateDiff(dd, [TIMESTAMP],GetDate())=0

    GROUP BY AccountNo

    ORDER BY UOM DESC

    What I need is all the information in that the above table ordered by which account has ordered the most. With a field at the end giving the total qty ordered. Something like this.

    Account Number QTY Name ROWID UOM

    123456 4 Example 1 10

    123456 6 Example 2 10

    654321 8 Example 1 17

    654321 9 Example 2 17

    Can anyone help me with this or tell me that it isn't possible.

    Many Thanks in advance

    Mark D

  • SELECT Account Number,QTY,Name,ROWID, SUM(CONVERT(INT,QTY)) OVER(PARTITION BY AccountNo) AS UOM

    FROM tfrOrderTransmissionDetailLog

    WHERE DateDiff(dd, [TIMESTAMP],GetDate())=0

    ORDER BY UOM DESC

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • That works perfectly. Thanks very much

  • One small observation..

    Its not a good idea to use a function in your WHERE clause if you can avoid it as it makes the query non-SARGable

    You can modify Mark's code to something like this to make it SARGable..

    DECLARE@start_date DATETIME,

    @end_date DATETIME

    SET@start_date = DATEADD( DAY, DATEDIFF( DAY, 0, CURRENT_TIMESTAMP ), 0 )

    SET@end_date = DATEADD( DAY, 1, @start_date )

    SELECTAccount Number,QTY,Name,ROWID, SUM(CONVERT(INT,QTY)) OVER(PARTITION BY AccountNo) AS UOM

    FROMtfrOrderTransmissionDetailLog

    WHERE[TIMESTAMP] >= @start_date AND [TIMESTAMP] < @end_date -- This is a better way to do it

    ORDER BY UOM DESC


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Thanks, I haven't come across that term before, Glad you mentioned it.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply