CODEWITHSUNDEEP
linqlinq-to-sqllinq-group
Ben
Ben

Reputation: 4319

How do I get a count of values by month in linq to sql?

I have a table of feedback scores, which essentially contains a date field and a "score" field (which may be "happy", "neutral" or "sad").

I want to return a query which gives me the count of each score by month, like this:

enter image description here

However, my query isn't grouping correctly - I'm basically getting three rows for each month (one row for "happy", one for "neutral" and one for "sad", like this:

enter image description here

How do I aggregate the data together?

My query at the moment is this:

var monthlyScore = from f in db.tl_feedbacks
    group f by new { month = f.timestamp.Month, year = f.timestamp.Year, score = f.tl_feedback_score.score } into g
     select new
            {
              dt = string.Format("{0}/{1}", g.Key.month, g.Key.year),
              happyCount = g.Where(x => x.tl_feedback_score.score == "happy").Count(),
              neutralCount = g.Where(x => x.tl_feedback_score.score == "neutral").Count(),
              sadCount = g.Where(x => x.tl_feedback_score.score == "sad").Count(),
              total = g.Count()

          };

Upvotes: 1

Views: 1172

Answers (1)

Robert McKee
Robert McKee

Reputation: 21487

Remove , score = f.tl_feedback_score.score from your grouping.

Upvotes: 2

Related Questions