Group by and order by 1

[Dashley]

I'm running a query in a vb.net project

SELECT DATENAME(Month, DTE) AS mydate, SUM(amount) AS Tamt
FROM Checks
GROUP BY DATENAME(Month, DTE)

Getting the results as:

February 5913.3600
January 20096.1200
March 3561.6300

I want to order them by month (January, February, march etc.). I've tried order by DATEPART(Month, DTE) and then Month(DTE)

SELECT DATENAME(Month, DTE) AS mydate, SUM(amount) AS Tamt, DATEPART(Month, DTE) AS Expr1
FROM Checks
GROUP BY DATENAME(Month, DTE)
ORDER BY Expr1


with no luck Just keep getting
the standard can only contain aggregate function error.

Thanks

 

[gmmastros]

You can only order by columns returned in the select clause or the group by clause.

The only difference between DateName and DatePart is that one returns a string and the other returns a number. My point is, you can include this in the group by without actually changing the data that is returned.

Therefore....

SELECT   DATENAME(Month, DTE) AS mydate, 
         SUM(amount) AS Tamt
FROM     Checks
GROUP BY DATENAME(Month, DTE),
         [!]DATEPART(Month, DTE)[/!]
ORDER BY [!]DATEPART(Month, DTE)[/!]

By including the DatePart function in the group by, the data will be the same but you will now be able to sort on it.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Dashley]

Well It's nap time George.

I was adding DATEPART(Month, DTE) to the select statement then the order by. Duhh.

Thank you very much.