Show profit month after 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

General SQL Server Forums

New to SQL Server Programming

Show profit month after month

Author

Topic

deicer
Starting Member

2 Posts

Posted - 2011-01-23 : 15:55:43

Hi guys

I have a table like this:

id date amount
1 2009-04-01 149
2 2009-04-01 100
3 2009-05-17 200
4 2009-05-25 74
5 2009-06-03 123
6 2009-06-03 321
..
99 2010-11-29 845

I would like to make a SQL to show me this:

Year Month Amount
2009 04 249
2009 05 274
2009 06 434
...
2010 11 845

I have tried with:

SELECT SUBSTR(date,1,4) AS YEAR,SUM(AMOUNT) AS AMOUNT,     
SUBSTR(date,6,2) AS MONTH                                     
FROM MY_TABLE WHERE ID >' '                             
GROUP BY date

But i get a result like this:

YEAR AMOUNT MONTH
2009 4957300 04
2009 62300 04
2009 17800 04
2009 62300 04
2009 26700 04
2009 26700 04
2009 8900 04
2009 35600 04
2009 17800 04
2009 44500 04
2009 35600 04
2009 26700 04
2009 8900 04
2009 44500 04
2009 17800 04
2009 17800 04
2009 17800 04
2009 26700 04
2009 53400 04
2009 8900 04
2009 26700 04
2009 26700 04
2009 53400 04
2009 8900 04
2009 35600 04
2009 8900 04
2009 8900 04
2009 44500 04
2009 4165200 05
2009 62300 05

Any hints on how to fix it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-23 : 16:05:44

SELECT DATEPART(MONTH, [Date]) AS [Year], DATEPART(MONTH, [Date]) AS [Month], SUM(Amount) AS Amount
FROM MY_TABLE
GROUP BY DATEPART(MONTH, [Date]), DATEPART(MONTH, [Date])
ORDER BY DATEPART(MONTH, [Date]), DATEPART(MONTH, [Date])

N 56°04'39.26"
E 12°55'05.63"

deicer
Starting Member

2 Posts

Posted - 2011-01-23 : 16:16:44

Thanks, but I get an error on the SQL:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Date]) AS [Year], DATEPART(MONTH, [Date]) AS [Month], SUM(Amount) AS Amount FRO' at line 1

And isn't this the same, just with a different keyword:

DATEPART(MONTH, [Date]) AS [Year], DATEPART(MONTH, [Date]) AS [Month]

EDIT: I'm using MySQL and DATEPART is not supported. Wrong forum. Sorry

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-24 : 03:08:24

In Mysql use extract function

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources