SELECT CASE and GROUP BY Problem

[jadoger]

I am working on a denormalized table

ContactID Order_Order_Date ParticipantDate
1 20/12/2001 NULL
1 NULL 12/10/2002
1 29/12/2001 NULL
.....

Query I am firing is

select CASE Order_Order_Date WHEN NULL THEN 0 ELSE Count( Order_Order_Date) END, contact_id
from DenormalizedContactDetailsTest
group by contact_id,CASE Order_Order_Date WHEN NULL THEN Count( Order_Order_Date) ELSE 0 END
order by contact_id

gives me this error
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

which is understandable as aggregates are not allowed in goup by clause, my problem is I want to calculate number of orders any contact has placed, now my table has Order_Order_Date field which can be null and I want tp calculate only where Order_Order_Date is not null grouped by contact_id

like

Contad Id OrdersPlaced
1 10
2 0

I can't use
group by Contact_Id,Order_Order_Date
as it will break down the aggregation in to diffrerent dates for same contact_id, but I still have to calculate Order_Order_Date in select's case statement.

any help?
jadoger

 

[collierd]

Could you use:

select distinct contact_id, count(order_order_date)
from  DenormalizedContactDetailsTest
group by contact_id, order_order_date
order by contact_id

Don't have your db so can't test it


Damian.