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.
produces ...
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
How can I convert the below to Linq, I've made a few attempts at it with no joy. 1 aggregate function count I can do but not two.
Thanks for replies
Code:
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, COUNT(CASE WHEN Extra = 1
THEN 'HasExtra'
ELSE NULL END) AS ExtraCount
from CartItems
group by ProductID;
produces ...
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
How can I convert the below to Linq, I've made a few attempts at it with no joy. 1 aggregate function count I can do but not two.
Code:
select ProductID, count(ProductID) as Total, COUNT(CASE WHEN Extra = 1
THEN 'HasExtra'
ELSE NULL END) AS ExtraCount
from CartItems
group by ProductID;
Thanks for replies