group by - but flag if particular value exists 1

[philb007]

Hi,

I HAVE 3 fields, CUSTOMER_ID, COLOUR and price

CUSTOMER_ID COLOUR price
1 RED 13
1 BLUE 23
2 YELLOW 4
2 BLUE 54
2 RED 35
ETC.

I have a group by cutomer_id and want to return for that customer a flag to say if they chose a particular colour (say RED)

I can do max(colour) as an aggregate function, but that doesn't help me.

How can I say 'if colours for this customer includes 'red' then 1 AS chose_red.


something along the lines of....(but that will work)

select customer_id, avg(price),max(colour),min(colour),case when colour includes 'red' then 1 else o end as chose_red
group by customer_id

 

[gmmastros]

Ah.... you're close.

The problem is... your case statement is not an aggregate. When you use group by, all of the columns returned in the select clause must be included in the group by clause OR an aggregate (like min, max, sum, avg, etc). There are a couple things you could do depending on what you want. Ex:

select customer_id, avg(price),max(colour),min(colour),
       [!]Count(case when colour = 'red' then 1 end as RedCount[/!],
       [blue]Max(Case when Colour = 'red' Then 1 Else 0 End) As Chose_red[/blue]
group by customer_id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Philb007,

Try

select customer_id, 
       avg(price),
       max(colour),
       min(colour),
       case when exist(select * from yourtable where colour = 'red' and customerid = t.customerid) then 1 else o end as chose_red
from yourtable t
group by customer_id

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)