sum by month grouping - 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)

sum by month grouping

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2011-10-27 : 09:54:02

Hi there,

I want to sum amounts by months. However, I want to sum values by the 2nd of each month to the 1st of next month for all months converned.

Does anyone know what the easiest way to do this is?

Thanks for your help

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 10:00:12

One of the thing I tell people is to start small...build out the first component you know you need..in this case..sum by month

what have you tried so far?

Can you post table DDL, sample data in DML Form and then expect results???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cidr
Posting Yak Master

207 Posts

Posted - 2011-10-27 : 10:03:52

create table dates
( datecol datetime,
Sales decimal(10,4)
)

insert into dates
values('2010-01-01',14.00),
('2010-01-05',14.00),
('2010-01-01',14.00),
('2010-01-10',14.00),
('2010-01-11',14.00),
('2010-02-17',11.00),
('2010-02-01',14.00),
('2010-02-02',15.00),
('2010-02-20',14.00),
('2010-02-27',17.00),
('2010-02-08',14.00),
('2010-02-28',14.00)

select sum(Sales)
from dates
group by MONTH(datecol)

I should really know how to do this but my mind is not working for me on this one

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 10:05:04

I'm sure there must be more to this but...



CREATE TABLE myTable99(Col1 char(10), Col2 datetime, Col3 money)
GO

INSERT INTO myTable99(Col1, Col2, Col3)
SELECT 'A', '09/11/2011', 50.00 UNION ALL
SELECT 'A', '09/12/2011', 50.00 UNION ALL
SELECT 'A', '09/13/2011', 50.00 UNION ALL
SELECT 'B', '10/11/2011', 50.00 UNION ALL
SELECT 'C', '11/11/2011', 50.00
GO

SELECT MONTH(Col2), SUM(Col3)
FROM myTable99
GROUP BY MONTH(Col2)
GO 

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cidr
Posting Yak Master

207 Posts

Posted - 2011-10-27 : 10:33:19

Thanks Brett. However, what I'm asking for is months grouped together only by the 2nd day of the month to the 1st day of the next month for example:

2/10/2011 to 2/11/2011
So, everything from from the 2nd day up to and including the 1st of the next month... for each month

Is this something you can help with?

Cheers

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 10:50:00

What would you like to call this "Grouping"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

cidr
Posting Yak Master

207 Posts

Posted - 2011-10-27 : 11:18:28

em... I don't understand the question Brett. the name of the grouping is irrelevant, it's the technique to fetch the sum amount for the given groups of dates.

Let's call it closing balance

Cheers

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-27 : 12:36:01

Do you want to worry about the year as well?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 12:44:16

[code]
SELECT MONTH(DATEADD(dd,-1,Date)) AS MonthName,SUM(Col3)
FROM table
GROUP BY MONTH(DATEADD(dd,-1,Date))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cidr
Posting Yak Master

207 Posts

Posted - 2012-09-29 : 16:28:37

Came across this post again and never did say thanks for the help

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:36:53

no problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources