group by help - 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)

group by help

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-27 : 05:55:04

Hi all,

I have a query as below

select id,sum(cont_amount),
cont_date,
'FY:'convert(char(4),year('2007/01/01',101) + ' ' +
convert(varchar,cont_amount)
from #temp
Group by id,cont_date,
'FY:'convert(char(4),year('2007/01/01',101) + ' ' +
convert(varchar,cont_amount)

Out Put
-------

1 100587 2007-06-14 FY: 2007 100587
1 148163 2007-06-14 FY: 2007 148163
.
.
.

The above is summing up properly, however It should also sum up if the date is same for that id.

Acutally I want out put as below

1 248750 2007-06-14 FY: 2007 248750
.
.

I tried changing convert(varchar,sum(cont_amount))

but giving err.

Kindly help.

Regards,
aak

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 06:00:27

quote:
Originally posted by aakcse

I have a query as below

select id,sum(cont_amount),
cont_date,
'FY:'convert(char(4),year('2007/01/01',101) + ' ' +
convert(varchar,cont_amount)
from #temp
Group by id,cont_date,
'FY:'convert(char(4),year('2007/01/01',101) + ' ' +
convert(varchar,cont_amount)

No you don't. The query will not even parse with syntax.

Try something similar to this

SELECT		id,
		SUM(cont_amount),
		cont_date,
		'FY: ' + DATENAME(YEAR, cont_date) + CONVERT(VARCHAR(20), SUM(cont_amount))
FROM		#Temp
GROUP BY	id,
		cont_date

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

aakcse
Aged Yak Warrior

570 Posts

Posted - 2009-02-27 : 06:23:22

Thanks Peso

Done it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources