score:0

Accepted answer

methinks you not quite understood what i mean in comment. so try add more explained sample

first variant group new {d, s}

var sales= from d in cxt.sopdespatchreceiptlines 

join l in cxt.soporderreturnlines on d.soporderreturnlineid equals l.soporderreturnlineid
join o in cxt.soporderreturns on l.soporderreturnid equals o.soporderreturnid
join s in cxt.stockitems on l.itemcode equals s.code
join p in cxt.productgroups on s.productgroupid equals p.productgroupid
join c in cxt.slcustomeraccounts on o.customerid equals c.slcustomeraccountid
where d.despatchreceiptdate.year ==2014
orderby d.soporderreturnline.itemcode
//group d by d.soporderreturnline.itemcode into g

group new {d,s} by s.itemid into g

select new
{
    itemid = g.key,
    total = g.sum(d=>d.d.despatchreceiptquantity),
    average = (g.sum(d=>d.d.despatchreceiptquantity))/3,
    jan = g.sum(d => d.d.despatchreceiptdate.month==01 ? d.d.despatchreceiptquantity : 0),
    feb= g.sum(d => d.d.despatchreceiptdate.month==02 ? d.d.despatchreceiptquantity : 0),
    mar= g.sum(d => d.d.despatchreceiptdate.month==03 ? d.d.despatchreceiptquantity : 0),
    stock = g.first(s=>s.s)
};

return sales;

second way by new {s.itemid, s.freestockquantity}

var sales= from d in cxt.sopdespatchreceiptlines 

join l in cxt.soporderreturnlines on d.soporderreturnlineid equals l.soporderreturnlineid
join o in cxt.soporderreturns on l.soporderreturnid equals o.soporderreturnid
join s in cxt.stockitems on l.itemcode equals s.code
join p in cxt.productgroups on s.productgroupid equals p.productgroupid
join c in cxt.slcustomeraccounts on o.customerid equals c.slcustomeraccountid
where d.despatchreceiptdate.year ==2014
orderby d.soporderreturnline.itemcode
//group d by d.soporderreturnline.itemcode into g

group d by new {s.itemid, s.freestockquantity} into g

select new
{
    itemid = g.key.itemid,
    total = g.sum(d=>d.d.despatchreceiptquantity),
    average = (g.sum(d=>d.d.despatchreceiptquantity))/3,
    jan = g.sum(d => d.d.despatchreceiptdate.month==01 ? d.d.despatchreceiptquantity : 0),
    feb= g.sum(d => d.d.despatchreceiptdate.month==02 ? d.d.despatchreceiptquantity : 0),
    mar= g.sum(d => d.d.despatchreceiptdate.month==03 ? d.d.despatchreceiptquantity : 0),
    stock = k.key.freestockquantity
};

return sales;

Related Query