Group by month query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group by month query

Author

Topic

lilb321
Starting Member

2 Posts

Posted - 2011-03-23 : 10:33:46

Here is an example of my current table.

BuildingID, BuildingName, PeriodEndingDate, ElectricityUse
1, Oak Grove, 01/31/11, 279015
1, Oak Grove, 02/28/11, 264002
2, Ferndale, 11/30/10, 155234
5, Winston, 2/28/11, 345980
1, Oak Grove, 12/31/10, 299980

I would like to run a query that returns the following table for a specific BuildingID. In this example the BuildingID = 1

MonthNumber, MonthName, 2010_Elec_Usage, 2011_Elec_Usage
1, Jan, 230498, 279015
2, Feb, 235678, 264002
3, Mar, 240567, 288976

I figured out how to get the month columns, but that was about it.

SELECT MonthNumber, MonthName
FROM
(SELECT *
,DATEPART(mm,[Period Ending Date]) AS MonthNumber
,DATENAME(mm,[Period Ending Date]) AS MonthName
FROM Utilities) AS t
GROUP BY MonthNumber, MonthName
ORDER BY MonthNumber

I'm still a beginner with sql, so any help is appreciated.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-23 : 10:39:29

[code]
select *
from (
select MonthNumber = datepart(month, PeriodEndingDate),
MonthName = datename(month, PeriodEndingDate),
UsageYear = datepart(year, PeriodEndingDate),
ElectricityUse
from tbl
) d
pivot
(
sum(ElectricityUse)
for UsageYear in ([2010], [2011])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lilb321
Starting Member

2 Posts

Posted - 2011-03-23 : 16:46:58

Thanks! It worked great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources