August 16, 2006 at 9:24 am
Hello,
I'm trying to follow an example in BOL using CONVERT so that the numeric data that the query produces will contain a comma every third digit. The following is the query I'm working with;
select
sum(sumTxns) as totalTxns,
--sum (convert(decimal(15,2),sumSpend, 1 )) as totalSpend
sum(sumSpend) as totalSpend
from
(
select
[month],
--sum(cast(txns as money)) as sumTxns,
sum(cast (txns as int)) as sumTxns,
--sum(round(cast(spend as decimal (15,2)), 5)) as sumSpend
sum(cast(spend as decimal(15,2))) as sumSpend --2383270341.92(2),
--sum(cast(spend as money)) as sumSpend
--sum(spend) as sumSpend
from
PySummaryBymo
group by
[month]
)
as a
How can I set up 'totalTxns' and 'totalSpend' in the outer query so that I have commas formatted with commas?
Thank you for your help!
CSDunn
August 16, 2006 at 10:10 am
in the inner query, cast the values to be a money datatype. then on the outer datatype, peform the following query using the convert function:
select
convert(varchar, sum(sumTxns), 1) as totalTxns,
convert(varchar, sum(sumSpend), 1) as totalSpend
from (
select
[month],
sum(cast(txns as money)) as sumTxns,
sum(cast(spend as money)) as sumSpend
from
PySummaryBymo
group by
[month]
) a
the 1 is the style of the money type in the convert function. this will give you the commas and decimal that you want...
Chuck
August 16, 2006 at 10:16 am
Thank you for your help!
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply