CODEWITHSUNDEEP
sqlsql-servert-sql
tumultous_rooster
tumultous_rooster

Reputation: 12550

How can I get a value of 0 for records that don't satisfy the WHERE clause?

I have a simple SELECT statement, in t-sql:

SELECT Category, count(Category) as Total 
FROM mytable 
WHERE gender='F'
GROUP BY Category

Which gives me

Category     Total
make up       10

This if fine, but I also have a Class_B category. Class_B is made up only of gender=M. So, although there is no Class_B and I'm not matching any records, I would regardless like the returned table to be like:

Category     Total
make up       10
shaving kit   0

Any advice would be appreciate.

EDIT: major update to make this more clear. Sorry about that.

Upvotes: 1

Views: 133

Answers (4)

Stephan
Stephan

Reputation: 6018

Here is a simple and efficient way of doing it.

SELECT  Category, 
        SUM(CASE WHEN gender='F' THEN 1 ELSE 0 END) AS Total
FROM mytable 
GROUP BY Category

Upvotes: -1

Kritner
Kritner

Reputation: 13765

From your original query:

SELECT Category, count(Category) as Total 
FROM mytable 
GROUP BY Category

all categories that exist in the table would be represented. What you would actually need is a join on a table that actually contains all your potential categories. Something to the affect of:

SELECT c.category, count(m.*) as total
from categories c
left join myTable m on c.categoryId = m.categoryId
group by c.category

I'm a bit unclear based on your edits... but this might work

select m.category, isnull(subQuery.total, 0)
  from mytable m
  left join (
    select category, count(1) as total
    from mytable
    where gender = 'f'
    group by category
    ) subQuery on m.category = subQuery.category
  group by m.category, subQuery.total

http://sqlfiddle.com/#!6/6b0a9/15

Upvotes: 2

paparazzo
paparazzo

Reputation: 45096

if you can add in what makes the row unique this might work

SELECT t1.Category, count(distinct(t2.name)) as Total 
  FROM mytable t1 
  left join mytable t2  
    on t2.category = t1.category 
   and t2.gender='F'
 GROUP BY t1.Category

Upvotes: 0

sam yi
sam yi

Reputation: 4934

Will this work?

; WITH FullCategory AS (
    SELECT DISTINCT Category
    FROM mytable
), FemaleCategoryCount AS (
    SELECT Category, count(Category) as Total 
    FROM mytable 
    WHERE gender='F'
    GROUP BY Category 
)
SELECT fc.Category, COALESCE(fcc.Total, 0) Total
FROM FullCategory fc
LEFT JOIN FemaleCategoryCount fcc
    ON fc.Category = fcc.Category

Upvotes: 1

Related Questions