Formatting Compute Sum with Commas

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 2000 Forums

SQL Server Development (2000)

Formatting Compute Sum with Commas

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-03 : 07:46:41

Greg writes "I have the following query that returns that values I am expecting. I have the first 2/3 of my query returning in a numeric commas format (XXX,XXX,XXX.XX).

I want to get the COMPUTE SUM output from the last line of my query to also be in the same numeric commas format (XXX,XXX,XXX.XX).

Do you know how I can do this? I am on Windows 2000 SQL 2000 SP3. Thanks.

SELECT disp, convert(varchar(20),sum(amount),1)
FROM items
WHERE prcdate=convert(varchar, getdate(),101) and disp='+' and editstatus<>2
GROUP by disp

SELECT disp, sum(amount),convert(varchar(20),sum(amount),1)
FROM items
WHERE prcdate=(convert(varchar, getdate(),101)) and disp<>'+' and disp<>'B' and disp<>'R' and editstatus<>2
GROUP BY disp
ORDER BY disp
COMPUTE SUM(sum(amount))"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-03 : 08:07:23

DONT use SQL to format data, this job belongs to the presentation layer. Not only is it easier, it also affords you more space for customizing the formats depending on locales or user preferences.

Owais

Make it idiot proof and someone will make a better idiot

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-03 : 08:18:06

Also look in Books Online for "CUBE" and "ROLLUP". They perform the same thing as COMPUTE but do it as part of the regular resultset, so it needs no special handling.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources