CODEWITHSUNDEEP
c#sqlasp.net-mvclinq
Citizen SP
Citizen SP

Reputation: 1411

Group by percentage in LINQ

I try to create the following SQL query in LINQ to use in my ASP MVC project:

SELECT State, (Count(a.State)* 100 / (Select Count(*) From myTable))
FROM myTable a
GROUP BY a.State

What I have so far:

var data = db.myTable.GroupBy(fu => fu.State)
        .Select(g => new { Label = g.Key, Value = g.Key * 100 / g.Count() })
        .ToList();

The calculation is not correct. Have to get LINQ producing the same results as SQL?

Upvotes: 3

Views: 2709

Answers (2)

ocuenca
ocuenca

Reputation: 39326

You can try this:

var data = db.myTable.GroupBy(fu => fu.State)
        .Select(g => new { Label = g.Key, Value = g.Count() * 100 / db.myTable.Count() })
        .ToList();

g.Count() is going to give you Count(a.State) value and db.myTable.Count() the total of rows in that table

Upvotes: 1

Rahul Singh
Rahul Singh

Reputation: 21795

Probably this:-

 Value = g.Count() * 100 / db.myTable.Count()

This seems to be equivalent of your SQL query.

So your complete query should look like:-

var data = db.myTable.GroupBy(fu => fu.State)
         .Select(g => new { Label = g.Key, Value = g.Count() * 100 / db.myTable.Count() })
         .ToList();

Upvotes: 7

Related Questions