December 15, 2015 at 1:20 pm
-- CREATE A TEMP TABLE AND LOAD IT WITH DATA
select col1, col2, col3 into #T1 from
(
select 1 as col1, 2 as col2, 14 as col3 union all
select 2, 13, 20 union all
select 3, 4,null union all
select 4, 22,5 union all
select null, null, null union all
select 5, null ,4 union all
select null,5,3 union all
select 15,9,2 union all
select 16,null,1 union all
select 17,null,null
) x
-- NOW RUN THE QUERY AGAINST THE TEMP TABLE
select col1,col2,col3, case rn % 2 when 0 then 'even' when 1 then 'odd' end as 'Row Num',
case rn % 2 when 0
-- When an even row take the sum of columns col1, col2, col3
then sum (col1) over(partition by rn) + sum (col2) over(partition by rn) + sum (col3) over(partition by rn)
-- When an odd row count the number of columns having a non-null value
else count(col1) over(partition by rn) + count(col2) over(partition by rn) + count(col3) over(partition by rn) end as Answer
from
(
selectcol1,col2,col3, row_number() over (order by col1) as rn
from#T1
) x
order by rn
-- In the result set, look at row #10, Answer column.
-- Its value is NULL, but the correct value is 17.
-- Aggregates ignore null values. So what is wrong?
Jerry D
December 15, 2015 at 1:52 pm
Jerry Day (12/15/2015)
-- CREATE A TEMP TABLE AND LOAD IT WITH DATAselect col1, col2, col3 into #T1 from
(
select 1 as col1, 2 as col2, 14 as col3 union all
select 2, 13, 20 union all
select 3, 4,null union all
select 4, 22,5 union all
select null, null, null union all
select 5, null ,4 union all
select null,5,3 union all
select 15,9,2 union all
select 16,null,1 union all
select 17,null,null
) x
-- NOW RUN THE QUERY AGAINST THE TEMP TABLE
select col1,col2,col3, case rn % 2 when 0 then 'even' when 1 then 'odd' end as 'Row Num',
case rn % 2 when 0
-- When an even row take the sum of columns col1, col2, col3
then sum (col1) over(partition by rn) + sum (col2) over(partition by rn) + sum (col3) over(partition by rn)
-- When an odd row count the number of columns having a non-null value
else count(col1) over(partition by rn) + count(col2) over(partition by rn) + count(col3) over(partition by rn) end as Answer
from
(
selectcol1,col2,col3, row_number() over (order by col1) as rn
from#T1
) x
order by rn
-- In the result set, look at row #10, Answer column.
-- Its value is NULL, but the correct value is 17.
-- Aggregates ignore null values. So what is wrong?
You are doing the equivalent of "17 + NULL + NULL"... NULL plus anything = NULL.
Keep in mind that COUNT & SUM behave differently when dealing with NULLs... For example...
DECLARE @Value INT = NULL;
SELECT
SUM(@Value) AS [Sum],
COUNT(@Value) AS [Count]
Sum Count
----------- -----------
NULL 0
So, row 5 (for example) works because you're saying 4 + 3 + 0
December 15, 2015 at 1:55 pm
Nothing is wrong.
You are partitioning by row number, which is unique in the source data - making the partition redundant. If you sum a column which contains a NULL and the sum contains nothing other than NULL, it will return a NULL.
Here is another version of your query, which returns the same results without any partitioning.
SELECT x.rn
,x.col1
,x.col2
,x.col3
,CASE x.rn % 2
WHEN 0
THEN 'even'
WHEN 1
THEN 'odd'
END AS 'Row Num'
,CASE x.rn % 2
WHEN 0
-- When an even row take the sum of columns col1, col2, col3
THEN x.col1 + x.col2 + x.col3
-- When an odd row count the number of columns having a non-null value
ELSE iif(x.col1 IS NULL, 0, 1) + iif(x.col2 IS NULL, 0, 1) + iif(x.col3 IS NULL, 0, 1)
END AS Answer
FROM (
SELECT t.col1
,t.col2
,t.col3
,row_number() OVER (
ORDER BY t.col1
) AS rn
FROM #T1 t
) x
ORDER BY x.rn
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 15, 2015 at 2:02 pm
Thanks for enlightening me!
I kinda knew there was a stupid error on my part, but I just could not see it!
Jerry D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply