New Posts New Posts RSS Feed: Sum and Grouping in Linq
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Sum and Grouping in Linq
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Topic: Sum and Grouping in Linq
    Posted: 29-Dec-2013 at 10:26am
How would i do the following in Linq. Here is the SQL

Select duesyearmontn, sum(duesamount) as duesrate
from duesowed
where socsecno = "999-99-9999' and duescatcd = 'D'
group by duesyearmonth
having sum(duesamount) <> 0
order by duesyearmonth desc



this is what i have so far

               var duesOwedQuery = _manager.DuesOweds
                    .Where(d => d.SocSecNo == member.SocSecNo && d.DuesCatCd.StartsWith("D"))
                    .OrderByDescending(d => d.DuesYearMonth);
 
                var duesOweds1 = duesOwedQuery.Execute<DuesOwed>();

how do i handle the group by and having clause?

Bill
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 08-Jan-2014 at 4:28pm
Hi Bill,

Try:
      var query = mgr.DuesOweds
        .Where(d => d.SocSecNo == member.SocSecNo && d.DuesCatCd.StartsWith("D"))
        .GroupBy(d => d.DuesYearMonth)
        .Select(d => new { duesYearMonth = d.Key, duesRate = d.Sum(d1 => d1.DuesAmount) })
        .Where(d => d.duesRate != 0)
        .OrderByDescending(d => d.duesYearMonth);
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down