CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3279082
user3279082

Reputation:

Unable to sum column values

I am using this below query to sum my column values.

Query:

select 
'$ ' + REPLACE( CONVERT(VARCHAR(32),cast(round(isnull(sum([Share_Invest]),0),0)as     MONEY),1), '.00', '') [Investment], 
'$ ' + REPLACE( CONVERT(VARCHAR(32),cast(round(isnull(sum([Profit_Share]),0),0)as MONEY),1), '.00', '') [Profit Amount],
[Variance] = '$ ' + REPLACE( CONVERT(VARCHAR(32),cast(round(isnull(sum([Share_Invest]-[Profit_Share]),0),0)as MONEY),1), '.00', '') 
from Finance

for first column its fine and sum is showing up. But my Profit_Share column has null values and datas too and this doesn't sum column values.

Can anyone correct me where I'm going wrong.

Upvotes: 0

Views: 432

Answers (1)

cyan
cyan

Reputation: 747

nothing wrong with your query,
but probably you will get trouble if the [Share_Invest] or [Profit_Share] values are in decimals ($50.05, $10.10)

Assumming the @finance table is your finance table:

 declare @finance table
(
 id int identity,
 [Share_Invest] money,
 [Profit_Share] money
)


insert into @finance
select '50.05', '10.00' union all
select '20.05', '5.00' union all
select null, '5.00' union all
select null, null union all
select '10.00', null union all
select null, null

-- your query
select 
'$ ' + REPLACE( CONVERT(VARCHAR(5),cast(round(isnull(sum([Share_Invest]),0),0)as     MONEY),1), '.00', '') [Investment], 
'$ ' + REPLACE( CONVERT(VARCHAR(5),cast(round(isnull(sum([Profit_Share]),0),0)as MONEY),1), '.00', '') [Profit Amount],
[Variance] = '$ ' + REPLACE( CONVERT(VARCHAR(5),cast(round(isnull(sum([Share_Invest]-[Profit_Share]),0),0)as MONEY),1), '.00', '') 
from @Finance

/*
Investment Profit Amount Variance
---------- ------------- ---------
$ 80       $ 20          $ 60
*/

-- modified query
select
'$ ' + cast(sum(isnull([Share_invest],0)) as nvarchar(5))  as [Investment]
,'$ ' + cast(sum(isnull([Profit_Share],0)) as nvarchar(5))  as [Profit Amount]
, '$ ' + cast(sum(isnull([Share_invest],0) - isnull([Profit_Share],0)) as nvarchar(max)) as [Variance]
from @finance

/*
Investment Profit Amount Variance
---------- ------------- ---------
$ 80.10    $ 20.00       $ 60.10
*/

I remove the cast (As money), and round

Upvotes: 1

Related Questions