How to adjust SQL aggregation by date query result to merge values in same level where nulls exist for some columns

Question

Sorry for the messy title but here is my problem description.

| id|dateKey|colA|colB|colD|colE|
+---+-------+----+----+----+----+
| 1 |180111 | 1  | 5  |Null|Null|
| 2 |180111 |Null|Null| 6  | 7  |
| 3 |180111 | 2  | 5  | 3  | 2  |

I have the above table data but I want the result to appear as below when data aggregated (e.g. sum) by date:

|dateKey|colA|colB|colD|colE|
+-------+----+----+----+----+
|180111 | 3  | 10 |9   |9   |

Is there a way to do it as I am struggling to achieve it?

Note: this query will be running on 10 Gb of data with date filters.

Do you have a sample query? You can use coalesce to convert null or empty record to zero — jned29, Jan 03 '19 at 05:28
You can find the answer and live demo her- https://rextester.com/MKYY62432 — Suraj Kumar, Jan 03 '19 at 05:38

score 3 · Accepted Answer · answered Jan 03 '19 at 05:29

A simple GROUP BY query with SUM should already meet your needs:

SELECT
    dateKey,
    SUM(colA) AS colA,
    SUM(colB) AS colB,
    SUM(colD) AS colD,
    SUM(colE) AS colE
FROM yourTable
GROUP BY dateKey;

The reason this should work is that SUM by default ignores NULL values, so only the numbers present should end up in the sum.

score 1 · Answer 2 · answered Jan 03 '19 at 05:42

You can also use ISNULL to ignore and convert null values to zero as shown below.

create table #temp (id int, dateKey int, colA int, colB int, colD int, colE int)
insert into #temp values (1,180111, 1 , 5 ,Null,Null),
( 2,180111, Null  , Null  , 6 , 7 ),
( 3,180111, 2 , 5 , 3 , 2  )

SELECT
    dateKey,
    SUM(ISNULL(colA,0)) AS colA,
    SUM(ISNULL(colB,0)) AS colB,
    SUM(ISNULL(colD,0)) AS colD,
    SUM(ISNULL(colE,0)) AS colE
FROM #temp
GROUP BY dateKey;

How to adjust SQL aggregation by date query result to merge values in same level where nulls exist for some columns

2 Answers2