Order by month number in a group by

Question

Post reply

Order by month number in a group by

jmmclaren

Valued Member

Points: 68
More actions
December 19, 2014 at 9:26 am

#313189

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

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

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

jmmclaren Valued Member Points: 68 More actions · Answer 2

jmmclaren

Valued Member

Points: 68

December 19, 2014 at 9:43 am

#1765752

Thanks

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

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)