Seems like a good time to mention one of our courses: <http://www.sqldownunder.com/Training/Courses/2> http://www.sqldownunder.com/Training/Courses/2
:) Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: [email protected] [mailto:[email protected]] On Behalf Of Greg Keogh Sent: Wednesday, 17 July 2013 7:14 AM To: ozDotNet Subject: Re: [OT] T-SQL GroupBy and Sum on a DateTime You can (must) use a Where clause if you refer to soh.OrderDate directly Good grief! You're right. I thought a WHERE was forbidden in a grouped query and only HAVING was allowed. A working sample is below (although monthly totals would start and end on month boundaries, this sample just proves the WHERE and grouping work) -- Greg SELECT COUNT(Id) As Count, SUM(CAST([Send] AS BIGINT)) AS SendTotal, SUM(CAST([Recv] AS BIGINT)) As RecvTotal, SUM(Elapsed) AS ElapsedTotal, DATEPART(YEAR,[Time]) AS LogYear, DATEPART(MONTH,[Time]) AS LogMonth FROM [Visit] WHERE [Time] BETWEEN '2012-07-29' AND '2013-03-02' GROUP BY DATEPART(YEAR,[Time]), DATEPART(MONTH,[Time]) ORDER BY LogYear DESC, LogMonth DESC
