need help with sql syntax (self join)

[pandatime]

Hi,

I would like to return a result set like so:

widget count(where clr = red) count(where clr = blue)
123 5 11
124 0 3
125 17 21
etc...

The table has a "widget" column and a color "clr" column. I would like to see counts of each widget by color, red or blue. I am not sure how to write this query:

select a.widget, count(a.widget), count(b.widget)
from myTable a join 
myTable b on a.widget = b.widget
where a.clr = 'red'
and b.clr = 'blue'
group by a.widget, b.widget
order by a.widget

Unfortunately, it is giving me the product of the counts, not the counts themselves, e.g.)

widget count(where clr = red) count(where clr = blue)
123 55 55
124 0 0
125 357 357

What am I doing wrong?

Thanks

 

[pandatime]

Er, nevermind.

Easily solved with case statement!

select widget,
count(case when clr = 'red' then 1 end),
count(case when clr = 'blue' then 1 end)
from myTable
group by widget
order by widget