Order by month number in a group by

  • Hi,

    Sample Data

    SET NOCOUNT ON;

    USE tempdb;

    GO

    CREATE TABLE CheckRegistry (

    CheckNumber smallint,

    PayTo varchar(20),

    Amount money,

    CheckFor varchar(20),

    CheckDate date);

    INSERT INTO CheckRegistry VALUES

    (1000,'Seven Eleven',12.57,'Food','2011-07-12'),

    (1001,'Costco',128.57,'Clothes','2011-07-15'),

    (1002,'Costco',21.87,'Clothes','2011-07-18'),

    (1003,'AT&T',69.23,'Utilities','2011-07-25'),

    (1004,'Comcast',45.95,'Utilities','2011-07-25'),

    (1005,'Northwest Power',69.18,'Utilities','2011-07-25'),

    (1006,'StockMarket',59.25,'Food','2011-07-25'),

    (1007,'Safeway',120.21,'Food','2011-07-28'),

    (1008,'Albertsons',9.15,'Food','2011-08-02'),

    (1009,'Amazon',158.34,'Clothes','2011-08-05'),

    (1010,'Target',89.21,'Clothes','2011-08-06'),

    (1011,'AT&T',69.23,'Utilities','2011-08-25'),

    (1012,'Comcast',45.95,'Utilities','2011-08-25'),

    (1013,'Nordstrums',259.12,'Clothes','2011-08-27'),

    (1014,'AT&T',69.23,'Utilities','2011-09-25'),

    (1015,'Comcast',45.95,'Utilities','2011-09-25'),

    (1016,'Northwest Power',71.35,'Utilities','2011-09-25'),

    (1017,'Safeway',123.25,'Food','2011-09-25'),

    (1018,'Albertsons',65.11,'Food','2011-09-29'),

    (1019,'McDonalds',12.57,'Food','2011-09-29'),

    (1020,'AT&T',69.23,'Utilities','2011-10-25'),

    (1021,'Comcast',45.95,'Utilities','2011-10-25'),

    (1022,'Black Angus',159.23,'Food','2011-10-25'),

    (1023,'TicketMasters',59.87,'Entertainment','2011-10-30'),

    (1024,'WalMart',25.11,'Clothes','2011-10-31'),

    (1025,'Albertsons',158.50,'Food','2011-10-31');

    This is the query.

    Select datename(month,CheckDate),

    SUM(Amount) As Total

    from CheckRegistry

    group by datename(month,CheckDate)

    How can I get the result orderd by the month number?

    Please Advice.

    Thanks

  • order by max(datepart(month,CheckDate))

    ____________________________________________________

    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
  • Thanks

  • Just in case you need to sort month names in the correct order, please see the following article...

    http://www.sqlservercentral.com/articles/T-SQL/71511/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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