Search code examples
sqlhana

Columns are aggregated incompletely

This is my table

ID|CREATED|VER     |
--+-------+--------+
61|  NULL |2023.4.6|
61|  NULL |  null  |

Did aggregation on all of the columns like this

SELECT ID,
 SUM(CASE WHEN CREATED IS NOT NULL THEN 1 ELSE 0 END) AS CREATED_AGG,
 SUM(CASE WHEN VER IS NULL THEN 1 ELSE 0 END) AS VER_AGG
FROM TABLE
GROUP BY ID, CREATED, VER

result

ID|CREATED_AGG|VER_AGG           |
--+-----------+------------------+
61|          0|                 0|
61|          0|                 1|

I think below is the expected result. Why does it look like it is not fully aggregated?

ID|CREATED_AGG|VER_AGG           |
--+-----------+------------------+
61|          0|                 1|
Solution

  • The result for the data and query provided is correct and reproducible on all standard compliant SQL databases.

    The NULLs in columns CREATED and VER are grouped into separate groups - thus resulting in multiple result rows - since NULL is not considered comparable to "other" NULLs. So, the NULLs are not in the same group.

    To achieve the desired outcome, grouping just by ID does the trick.