How to Group by Month - 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 2005 Forums

Transact-SQL (2005)

How to Group by Month

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2011-08-30 : 14:06:50

Hello All,
Is there a way to group the total by month?

CREATE TABLE #Test
(
InvDate datetime NOT NULL,
Amount money NOT NULL
)
GO

INSERT INTO #Test (InvDate, Amount) VALUES ('1/5/2011', 100.00)
INSERT INTO #Test (InvDate, Amount) VALUES ('1/10/2011', 200.00)
INSERT INTO #Test (InvDate, Amount) VALUES ('1/10/2011', 50.50)

INSERT INTO #Test (InvDate, Amount) VALUES ('2/1/2011', 300.00)
INSERT INTO #Test (InvDate, Amount) VALUES ('2/1/2011', 20.00)
INSERT INTO #Test (InvDate, Amount) VALUES ('2/15/2011', 80.00)

INSERT INTO #Test (InvDate, Amount) VALUES ('3/5/2011', 60.00)
INSERT INTO #Test (InvDate, Amount) VALUES ('3/20/2011', 40.00)

----------------------------------
SELECT InvDate, Sum(Amount)
FROM
#Test
GROUP BY InvDate

-- The result from this query is
2011-01-05 00:00:00.000 100.0000
2011-01-10 00:00:00.000 250.5000
2011-02-01 00:00:00.000 320.0000
2011-02-15 00:00:00.000 80.0000
2011-03-05 00:00:00.000 60.0000
2011-03-20 00:00:00.000 40.0000

but I want to group it by month as the result will look like this:
Jan-11 350.50
Feb-11 400.00
Mar-11 100.00

Thank you for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-30 : 14:47:09

[code]SELECT InvDate, Sum(Amount)
FROM
#Test
GROUP BY InvDate[/code]Replace the two instances of InvDate in red with any of these three, depending on how close you want to get to your requirement.

1. YEAR(InvDate), MONTH(InvDate)
2. CONVERT(varchar(6),InvDate,112)
3. LEFT( DATENAME(MONTH,InvDate),3)+'-'+ CAST( YEAR(InvDate)%100 AS VARCHAR(2))

Be sure to replace both occurrences with the same thing.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-30 : 14:49:27

select dateadd(month,datediff(month,0,InvDate),0) as InvMonth
,SUM(Amount) as Amont
FROM #test
GROUP BY dateadd(month,datediff(month,0,InvDate),0)

Will group things by months. The formatting of the result should be done in the front-end.

Jim

Everyday I learn something that somebody else already knew

nizguy
Starting Member

37 Posts

Posted - 2011-08-30 : 15:00:30

Works great!

Thank you both

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources