A quety with multiple counts 1

[achmo]

Hello,

I need help with the syntax of a query, and this is the simplefyed version of the problem:

let's say I have a database with sales information . I'm selling differen items, each one in three colors. Each record on the database represents one sale , and contains this information: ItemId and Color. The Color field can contain "red", "blue" or "green". What I need to get is a list of all items sold, with the number of red models, blue models and green models sold for each item. If I wanted to know how many red models where sold from each item, I would use:

select itemID, count(color)
where color="red"
group by itemId

But how do I get three different counts for each item, every count with it's color condition?

Hope this was clear, and thanks in advance!

Yael

 

[r937]

[tt[ct itemID
, sum( case when color="red"
then 1 else 0 end ) as reditems
, sum( case when color="blue"
then 1 else 0 end ) as blueitems
, sum( case when color="green"
then 1 else 0 end ) as greenitems
from salestable
group by itemId[/tt]

rudy

 

[r937]

oops, typo in TGML (Tecumseh Group Markup Language)

[tt]select itemID
, sum( case when color="red"
then 1 else 0 end ) as reditems
, sum( case when color="blue"
then 1 else 0 end ) as blueitems
, sum( case when color="green"
then 1 else 0 end ) as greenitems
from salestable
group by itemId[/tt]

 

[achmo]

wow, great, that was quick! thanks a million, i'll try it

Yael

 

[achmo]

it's exactly what I need. And how simple it looks when you know how...

you rule.

Yael