I'm not sure the title really describes my thread well but I couldn't think of a succinct way of describing my question so here's the long version:-

We have customers. Each customer can be interested in one or more products though they almost always are interested in just one. We refer to a customer with more than one interest as a multi product customer. A customers interest is stored in the customer interest table and a multi product customer is anyone who has more than one entry in this table. A Customer will always have at least one interest.

I'm writing a query for a report and it needs to group by a customer's product interests, except that multi-product will be a single grouping. i.e. We need a row for customer who are only interested in widgets, one for customers who are only interested in doobries, one for thingummies and one for customer who are interested in more than one product.

We're also grouping by several other fields but these are all single field foreign keys on the customer record so are pretty easy to handle. An example of this would be a customer's source (where we heard about them from).

In essence that means I need to do something like this:-
Code:
Select sourceID as Source,
         Case when (Select Count(*) 
                        From CustomerInterest CI 
                        Where CI.CustomerID = C.CustomerID) > 1
                        then 0 --We Use 0 to indicate Multi Product
                Else (Select ProductID 
                       From customerInterest CI
                       Where CI.CustomerID = C.CustomerID)
         End as CustomerInterest,
         Count(*) as CustomerCount
From Customer C
Group By sourceID
That won't work because the CustomerID used in the case is in the Select list but not in the Group By or an aggregate. I obviously don't want to group by Customer ID because then I'll get a single record for every customer. What I really want to do is group by :-
Code:
Case when (Select Count(*) 
                        From CustomerInterest CI 
                        Where CI.CustomerID = C.CustomerID) > 1
                        then 0 --We'll Use 0 to indicate Multi Product
                Else (Select ProductID 
                       From customerInterest CI
                       Where CI.CustomerID = C.CustomerID)
         End
But it's got a count in and you can't put an aggregate function in the group by clause.

Any suggestions on how to achieve this?

P.S. I was working on this just before I left and, now I'm home, I don't have a sql server machine in front of me so please try and look past any spelling mistakes etc I've made above. I've just typed straight into the post.



Edit> Just had a thought as I hit the send button. I can probably just group by the alias. E.g. Group by Source, CustomerInterest. I'll have to try that tomorrow to be sure.