CODEWITHSUNDEEP
c#linq
bd528
bd528

Reputation: 886

Linq Returning Sum of 2 separate columns in same query

The code below brings through a row for each InvNo, along with the sum of "SCharge" :0

var Values = from data in DtSet.Tables["tblCosts"].AsEnumerable()
            group data by new
            {
                InvNo = data.Field<double>("InvoiceNo"),
                AccRef = data.Field<double>("SiteRefNum"),
            }
            into g
            select new
            {
                Code = "1",
                InvType = "I",
                Account = g.Key.AccRef,
                InvNo = g.Key.InvNo,
                SCharge = g.Sum(d => d.Field<double>("SCharge"))
            };

Is there are way of modifying this so that I get another set of rows below, this time doing a sum on the column "TCharge", or would I need to do a new query?

Upvotes: 1

Views: 208

Answers (2)

Servy
Servy

Reputation: 203812

So since you want to show all of the rows twice, but with a slightly different projection the second time, we can start by saving a query for the groupings, and then performing two projections on those groups and combining them together.

var groupings = DtSet.Tables["tblCosts"].AsEnumerable()
                .GroupBy(data => new
                {
                    InvNo = data.Field<double>("InvoiceNo"),
                    AccRef = data.Field<double>("SiteRefNum"),
                }).ToList();

var Values = groupings.Select(g => new
{
    Code = "1",
    InvType = "I",
    Account = g.Key.AccRef,
    InvNo = g.Key.InvNo,
    Charge = g.Sum(d => d.Field<double>("SCharge"))
})
.Concat(groupings.Select(g => new
{
    Code = "1",
    InvType = "I",
    Account = g.Key.AccRef,
    InvNo = g.Key.InvNo,
    Charge = g.Sum(d => d.Field<double>("SCharge"))
}));

Upvotes: 2

Muhammad Hani
Muhammad Hani

Reputation: 8664

select new
     {
       Code = "1",
        InvType = "I",
        Account = g.Key.AccRef,
        InvNo = g.Key.InvNo,
        SCharge = g.Sum(d => d.Field<double>("SCharge")),
        TCharge = g.Sum(d => d.Field<double>("TCharge"))
     };

Upvotes: 0

Related Questions