BG101 (7/13/2015)
Basically I need to get the SUM of the sum of three columns and all three columns have nulls. To make it more complicated, the result set must return the top 20 in order desc as well.I keep facing different issues whether I try and use Coalesce, IsNull, Sum, count, anything. My query never returns anything but 0 or NULL regardless of if I am trying to build a CTE or just use a query.
So I'm using Col A to get the TOP 20 in order (which is fine) but also trying to add together the sums of Col A + Col B + Col C for each of the twenty rows... wtf?
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA AS
(
SELECT
ABS(NULLIF(CHECKSUM(NEWID()) % 16,0)) AS COL_1
,ABS(NULLIF(CHECKSUM(NEWID()) % 16,0)) AS COL_2
,ABS(NULLIF(CHECKSUM(NEWID()) % 16,0)) AS COL_3
FROM sys.all_columns SAC
)
SELECT
SUM
(
ISNULL(SD.COL_1,0)
+ ISNULL(SD.COL_2,0)
+ ISNULL(SD.COL_3,0)
) AS THE_SUM
FROM SAMPLE_DATA SD;