how do i removed NULL group by in SQL?-CodePudding

How do I remove the NULL row, or better yet, assign a unique value to that row, instead of NULL?

can you show me both way?

enter image description here

i try to use this as a fact table so i can connect a relationship on powerbi but i got a many to many relationship, i think assigning a value to the NULL might able to solve the problem

CodePudding user response：

You can include a where clause. After from, before group by.

where not HKDATE is null

CodePudding user response：

To remove the NULL group, filter it off:

SELECT HKDATE, COUNT(unitnbr) AS Move_Count
FROM [N4Misc].[dbo].[RMG_MOVEHIST]
WHERE HKDATE IS NOT NULL
GROUP BY HKDATE;

To keep the NULL group, but label it as something else, use COALESCE:

SELECT COALESCE(CONVERT(varchar(10), HKDATE, 120), 'MISSING') AS HKDATE,
       COUNT(unitnbr) AS Move_Count
FROM [N4Misc].[dbo].[RMG_MOVEHIST]
GROUP BY CONVERT(varchar(10), HKDATE, 120);