Grouping null and empty values as one in sql

Question

I have a query which provides a breakdown of different categories applied to things in the database:

select categories, count(*) from products group by categories

The data comes like this:

NULL 56 42 FooCategory 12 BlahCategory 2

I would like to group NULL and <blank>

NoCategory 98 FooCategory 12 BlahCategory 2

AbstractChaos · Accepted Answer · 2012-11-21 15:41:00Z

try

select ISNULL(categories,'') as Categories, count(*) from products group by ISNULL(categories,'')

UPDATE

see here for parameters required

ISNULL ( check_expression , replacement_value )

Beth · Accepted Answer · 2012-11-21 15:30:27Z

4

try

select categories, case when categories is null or categories = ' ' then 'noCategory' else categories end as grouped, count(*) from products group by grouped

answered Nov 21, 2012 at 15:30

Beth

9,6171 gold badge26 silver badges44 bronze badges

1 Comment

JAQFrost Over a year ago

This looks good to me, though I'd probably trim the column and compare to an empty string to match either '' or all whitespace.

Collectives™ on Stack Overflow

Grouping null and empty values as one in sql

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related