Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL to LINQ translation help!

Status
Not open for further replies.

Laeg

Programmer
Nov 29, 2004
95
IE
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.

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
 
I think you need something like this:

Code:
var set = new List<Test>()
{
	new Test(){CartItemId = 1, ProductId = 1, Extra = false},
	new Test(){CartItemId = 2, ProductId = 1, Extra = true},
	new Test(){CartItemId = 3, ProductId = 2, Extra = false},
	new Test(){CartItemId = 4, ProductId = 2, Extra = false}
};

var result = from t in set
		group t by t.ProductId into g
		select new {Id = g.Key, Total = g.Count(), ExtraCount = g.Count(p => p.Extra)};
assuming a class that looks like this:
Code:
class Test
{
	public Int32 CartItemId { get; set; }
	public Int32 ProductId { get; set; }
	public bool Extra { get; set; }
}

Note that you take the total count for "Total", then the count where a condition is met (in this case, Extra = true) for "HasExtra".

Hope it helps,

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top