Sum a converted column - 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

Sum a converted column

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-02-13 : 07:55:32

Hi,
I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?

select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 08:01:40

you need to fisrt sum it and then do the formatting if at all you're doing it in sql
Its ugly to do formatting it sql. bring values as numeric data itself in sql and do the formatting at front end if at al possible


select ID,Dates,
'$ ' + replace(convert(varchar(100), 
convert(money, Cost), 1), '.00', '') AS Cost,
Funded 
FROM
(
select ID, MAX(Dates) Dates,SUM(Cost) Cost, MAX(Funded) Funded 
from Application group by ID)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-02-13 : 08:03:21

select
ID,
MAX(Dates) Dates,
'$ ' + replace(convert(varchar(100), convert(money, sum(Cost)), 1), '.00', '') Cost,
MAX(Funded) Funded
from Application
group by ID

Too old to Rock'n'Roll too young to die.

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-02-13 : 08:58:40

quote:
Originally posted by webfred

select
ID,
MAX(Dates) Dates,
'$ ' + replace(convert(varchar(100), convert(money, sum(Cost)), 1), '.00', '') Cost,
MAX(Funded) Funded
from Application
group by ID

Too old to Rock'n'Roll too young to die.

I need to sum for similar ID values. If there are two rows for similar ID's, I need to sum.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-02-13 : 09:16:10

I believe you mean EQUAL IDs and not SIMILAR IDs.

Just give it a try and see the result - anything wrong?

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources