Prevent Grouping rows by NULL value

Question

When grouping with a column in a GROUP BY statement that contains NULLs, they will be put into one group in your result set:

However, what I want is to prevent grouping rows by NULL value.

The following code gives me one row:

IF(OBJECT_ID('tempdb..#TestTable') IS NOT NULL)
    DROP TABLE #TestTable
GO 

CREATE TABLE #TestTable ( ID INT, Value INT )

INSERT INTO #TestTable(ID, Value) VALUES 
(NULL, 70),
(NULL, 70)


SELECT 
  ID
, Value
FROM #TestTable 
GROUP BY ID, Value

The output is:

ID     Value
NULL    70

However, I would like to have two rows. My desired result looks like this:

NULL    70
NULL    70

Is it possible to have two rows with GROUP BY?

UPDATE:

What I need is to count those rows:

SELECT 
COUNT(1) AS rows
FROM (SELECT 1 AS foo
    FROM #TestTable 
    GROUP BY ID, Value
)q

OUTPUT: 1

But, actually, there are two rows. I need output to have 2.

You will need to provide better examples. Maybe with different values for `Value` and/or with some non-null values for `ID`- I for one have no idea what exactly you are trying to achieve. — , Apr 27 '20 at 12:53
@a_horse_with_no_name my table has above columns such as `ID` and `Value` and I am trying to GROUP BY these rows. My goal is to show these rows as different — Learner, Apr 27 '20 at 12:56
But those rows are **not** different. And again: what do you want as the result if you have more (different) values? — , Apr 27 '20 at 12:56
Do you need to count distinct values where `id` is null? because that can be done easily: `select count(distinct value) from #TestTable where id is null;` — Zohar Peled, Apr 27 '20 at 13:09
@ZoharPeled no, my goal is to treat NULL's as non equal values to count of rows. — Learner, Apr 27 '20 at 13:13
@a_horse_with_no_name I really cannot understand this behavior, but why `s SELECT CASE WHEN NULL=NULL THEN 1 ELSE 2 END` is `2`? — Learner, Apr 27 '20 at 13:15

score 4 · Accepted Answer · answered Apr 27 '20 at 14:01

4

What you need is a way to make NULL values in Id unique. Using the following code will make the values unique, but continue to group the non-NULL value by virtue of the default value for a case expression being NULL:

group by Id, case when Id is NULL then NewId() end, Value

answered Apr 27 '20 at 14:01

HABO

15,314
5
39
57

Searching for `t sql udf newid` with turns up [this](https://stackoverflow.com/a/775573/92546). – HABO Apr 27 '20 at 15:01
Yeah, that's better and shorter than my answer. +1. – Zohar Peled Apr 27 '20 at 19:09

score 2 · Answer 2 · answered Apr 27 '20 at 12:58

Assuming you want this behavior because you do want to group by the values of the nullable column (Id in your example), you can add a row_number when the id column is null using a common table expression to create an artificial difference between duplicate groups - like this:

-- Adding some more rows to the table
INSERT INTO #TestTable(ID, Value) VALUES 
(NULL, 70),
(NULL, 70),
(1, 70),
(1, 70),
(2, 70);

The query, with the cte:

WITH CTE AS
(
    SELECT Id, Value, IIF(Id IS NULL, ROW_NUMBER() OVER(ORDER BY Id), NULL) As Surrogate
    FROM #TestTable 
)
SELECT 
  ID
, Value
FROM CTE
GROUP BY ID, Surrogate, Value

Results:

ID      Value
NULL    70
NULL    70
1       70
2       70

Prevent Grouping rows by NULL value

2 Answers2