May 2, 2014 at 8:27 am
Hi,
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.
May 2, 2014 at 8:48 am
vigneshkumart50 (5/2/2014)
Hi,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.
what are the data types for "Share_Invest" and "Profit_Share"?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 2, 2014 at 9:14 am
the datatypes are of decimal(10,4)
May 2, 2014 at 9:22 am
What do you get if you run?
SELECT
SUM(ISNULL([Share_Invest],0)) [Investment],
SUM(ISNULL([Profit_Share],0)) [Profit Amount],
[Variance] = SUM(ISNULL([Share_Invest]-[Profit_Share],0))
FROM Finance
May 2, 2014 at 9:37 am
No I don't get sum for 2 nd column
May 2, 2014 at 9:39 am
vigneshkumart50 (5/2/2014)
No I don't get sum for 2 nd column
What do you mean you don't get sum? What do you get? Help us out here by giving us some details.
I agree with the last post but I think you would need to wrap both columns with an ISNULL.
SELECT
SUM(ISNULL([Share_Invest],0)) [Investment],
SUM(ISNULL([Profit_Share],0)) [Profit Amount],
[Variance] = SUM(ISNULL([Share_Invest], 0)- ISNULL([Profit_Share],0))
FROM Finance
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2014 at 9:48 am
vigneshkumart50 (5/2/2014)
No I don't get sum for 2 nd column
Have you checked to see if the values actually exist?
SELECT *
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
) d (Amount1, Amount2)
SELECT SUM(Amount1), SUM(Amount2)
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
) d (Amount1, Amount2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
some extra examples:
SELECT SUM(Amount2) - SUM(Amount1)
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
) d (Amount1, Amount2)
-- 730
SELECT SUM(Amount2-Amount1)
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
) d (Amount1, Amount2)
-- 450
SELECT SUM(ISNULL(Amount2,0)-ISNULL(Amount1,0))
FROM (VALUES (CAST(0 AS DECIMAL(10,4)),CAST(0 AS DECIMAL(10,4))), (10,100), (20,NULL), (NULL,300),(40,400)
) d (Amount1, Amount2)
-- 730
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 2, 2014 at 9:48 am
I find it far easier to understand if I have something to work with...
here is a very simple set up....based on these values...what do you expect from your query?
USE [tempdb]
GO
CREATE TABLE [Finance](
[Share_Invest] [decimal](10, 4) NULL,
[Profit_Share] [decimal](10, 4) NULL
) ON [PRIMARY]
GO
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (12.0000, 5.3300 )
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (5.6680, 2.193 )
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (7.950, NULL )
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (NULL, NULL)
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (NULL, 1.5 )
INSERT [Finance] ([Share_Invest], [Profit_Share]) VALUES (0.05, .005 )
SELECT * FROM [Finance]
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply