CODEWITHSUNDEEP
sql
birdy
birdy

Reputation: 9636

Grouping null and empty values as one in sql

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

Upvotes: 3

Views: 5435

Answers (2)

AbstractChaos
AbstractChaos

Reputation: 4211

try 

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

UPDATE

see here for parameters required

ISNULL ( check_expression , replacement_value )

Upvotes: 8

Beth
Beth

Reputation: 9607

try

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

Upvotes: 4

Related Questions