Regarding 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

General SQL Server Forums

New to SQL Server Programming

Regarding Grouping

Author

Topic

mailtosaja
Starting Member

28 Posts

Posted - 2008-08-13 : 09:28:33

Hi,
I have the table like below structure..
id Date effort
1 4-5-2005 5
1 7-5-2005 1
3 4-6-2005 2

i want group the date according to the month wise..
sample output..
1 6
3 2

Thanks & Regards,
S.Jess

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-13 : 09:29:16

look at DATEPART in Books Online

Em

mailtosaja
Starting Member

28 Posts

Posted - 2008-08-13 : 09:30:46

hi,

thanks & Regards,
S.Jess

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 09:30:52

SELECT ID, SUM(Effort) FROM Table1 GROUP BY ID ORDER BY ID

E 12°55'05.25"
N 56°04'39.16"

mailtosaja
Starting Member

28 Posts

Posted - 2008-08-13 : 09:31:16

hi,
Sorry , Can u send me query....
thanks & Regards,
S.Jess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:32:04

[code]SELECT id,SUM(effort) AS effort
FROM YourTable
GROUP BY id,DATEADD(mm,DATEDIFF(mm,0,Date),0)[/code]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-13 : 09:35:48

I don't see any grouping by any kind of date in the example.

- Jeff
http://weblogs.sqlteam.com/JeffS

mailtosaja
Starting Member

28 Posts

Posted - 2008-08-13 : 09:58:08

Hi,
i need the month wise effort ...Please Don't group id
Thanks & Regards,
S.Jess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 10:00:51

quote:
Originally posted by mailtosaja

Hi,
i need the month wise effort ...Please Don't group id
Thanks & Regards,
S.Jess

then what id value should you be getting in o/p? MIN or MAX for a month?Your posted sample data shows id values also

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 10:01:31

quote:
Originally posted by mailtosaja

Please Don't group id

Why not?
That's how your desired output is written...

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Date), '19000101'), SUM(Effort)
FROM Table1
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Date), '19000101')
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Date), '19000101')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources