Query Help

[aug4878]

Hi,

I have two tables Category and items. I have 5 categoried stored in the category table. Item table has (item id, item name and categeory id)

I have to display item name, item count in each category. If an item does does not belong to a category, the count should be zero. it should look like this...

ItemName Cat1 Cat2 Cat3 Cat4 Cat5
aaa 2 1 0 0 1
bbb 0 0 3 1 4


If the count is zero means, the item table has no values for the category.

Any suggestion ...will be help full

Warm Regards,
Thiyaga

 

[r937]

select i.ItemName
     , sum(case when c.categoryname = 'Cat1'
                then 1 else 0 end)  as Cat1
     , sum(case when c.categoryname = 'Cat2'
                then 1 else 0 end)  as Cat2
     , sum(case when c.categoryname = 'Cat3'
                then 1 else 0 end)  as Cat3
     , sum(case when c.categoryname = 'Cat4'
                then 1 else 0 end)  as Cat4
     , sum(case when c.categoryname = 'Cat5'
                then 1 else 0 end)  as Cat5
  from items as i
inner
  join categories as c
    on c.id = i.category_id
group
    by i.ItemName

r937.com | rudy.ca