Can this be done? 1

[Laeg]

I have a table of CartItems, with the ProductID bought and then an Extra field which is true or false. I want to see a grouping of a count of each ProductID but within that count I want to know the count of ProductIDs with extras.

So given

create table test(CartItemID int, ProductID int, Extra int);

insert into test (CartItemID, ProductID, Extra) values (100, 1, 1);
insert into test (CartItemID, ProductID, Extra) values (200, 2, 0);
insert into test (CartItemID, ProductID, Extra) values (300, 2, 0);
insert into test (CartItemID, ProductID, Extra) values (400, 1, 1);

select ProductID, count(ProductID) as Total
from test
group by ProductID;

produces...

ProductID | Total
1 2
2 2

I'd like it to say ...

ProductID | Total | ExtraCount
1 2 2 <-- True As 2 instances of a ProductID 1 purchase also had Extra (CartItemID 100, 400)
2 2 0 <-- False As 0 instances of a ProductID 2 purchase had Extra

Can this be done?

Thanks for replies

 

[r937]

SELECT ProductID 
     , COUNT(*) AS Total 
     , COUNT(CASE WHEN Extra = 1
                  THEN 'Science!!'
                  ELSE NULL END) AS ExtraCount
  FROM CartItems
GROUP
    BY ProductID

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Laeg]

Cheers! Now if only you could translate that into .NET Linq it'd make my day week!

 

[r937]

perhaps you should ask that question in the .NET forum, eh

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon