Linq group by multiple columns and sum them together

Question

Instead of writing a large explanation text i want to show it with images

How can i group this data into something like this:

enter image description here

Loosely with something like:

var obj = (
    from f in context.assets
    group f by new 
    {
        f.signed, 
        f.typeOfInvestment
    }
    into gvc
    select new
    {
        Signed = sum(Signed)
        Unsigned = Sum(Unsigned)
        NotAvailable = Sum(NotAvailable)
        SumOfWorth = Sum(Signed + Unsigned + NotAvailable)
    }).ToList();

All help would be appreciated

Sincerly agh

http://stackoverflow.com/questions/2405249/linq-how-to-transform-rows-to-columns-with-a-count-crosstab-data — Tassadaque, May 16 '12 at 11:18
http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq — Tassadaque, May 16 '12 at 11:20

Tilak · Accepted Answer · 2012-05-16T11:44:11.107

Following is code for pivoting the data

Linq Query

        Random rand = new Random();
        var data = Enumerable.Range(0,15).Select(x=> new {Company= "Company" + rand.Next(1,100),
                    Signed="Sign" + rand.Next(1,4), StockType= "Type"+rand.Next(1,4), Worth=rand.Next(1000,2000)});

        var sampleData = data.GroupBy(x=>x.StockType).Select( x=> new {StockType=x.Key, Signed = x.Count()});

        var pivotData = data.GroupBy(x=>x.StockType).Select( 
        x=> 
        new 
            {   StockType=x.Key, 
                Signed = x.Where(y=>y.Signed=="Sign1").Count(),
                UnSigned = x.Where(y=>y.Signed=="Sign2").Count(),
                NA = x.Where(y=>y.Signed=="Sign3").Count(),
                Total = x.Where(y=>y.Signed=="Sign1"||y.Signed=="Sign2"||y.Signed=="Sign3").Count()

            });

UnPivot data (simulated input)

        Company Signed StockType WorthΞΞ 
    Company42 Sign1 Type3 1884 
    Company35 Sign2 Type3 1437 
    Company30 Sign1 Type2 1781 
    Company50 Sign2 Type3 1747 
    Company57 Sign1 Type2 1116 
    Company32 Sign1 Type2 1350 
    Company83 Sign3 Type2 1027 
    Company2 Sign1 Type3 1983 
    Company74 Sign1 Type2 1690 
    Company77 Sign3 Type1 1502 
    Company2 Sign1 Type1 1797 
    Company18 Sign1 Type1 1768 
    Company39 Sign1 Type1 1715 
    Company1 Sign1 Type2 1143 
    Company50 Sign2 Type1 1420

Output (Not completely pivoted, but as per this question requirement)

        StockType Signed1 Signed2 Signed3 TotalΞΞ 
        Type2 1 0 2 3 
        Type1 2 3 1 6 
        Type3 3 2 1 6

Hi exactly what i was looking for, instead of count i used SUM because this is money amount. — aghaux, May 16 '12 at 12:59

score 1 · Answer 2 · answered May 16 '12 at 11:52

First of all, you don't need to group by Signed property. Second, here's the query:

var result = 
from g in context.Assets.GroupBy(x=>x.StockType)
let signed = g.Where(x => x.Signed == "signed").Sum(x=>x.Worth)
let unsigned = g.Where(x => x.Signed == "unsigned").Sum(x => x.Worth)
let notAvailable = g.Where(x => x.Signed == "notAvail").Sum(x => x.Worth)
select new
{
    Signed = signed,
    Unsigned = unsigned,
    NotAvailable = notAvailable,
    SumOfWorth = signed + unsigned + notAvailable
};

score 1 · Answer 3 · answered May 16 '12 at 12:01

The code:

public class MainClass
{
    enum SignEnum { signed, unsigned, notAvail }
    class Element
    {
        public string Company;
        public SignEnum Signed;
        public string TypeOfInvestment;
        public decimal Worth;
    }
    class GroupedResult
    {
        public string TypeOfInvestment;
        public decimal signed, unsigned, notAvailable;
        public decimal Sum;
    }
    public static void Main()
    {
        List<Element> elements = new List<Element>()
        {
            new Element { Company = "JPMORGAN CHASE", Signed = SignEnum.signed,
                TypeOfInvestment = "Stocks", Worth = 96983 },
            new Element { Company = "AMER TOWER CORP", Signed = SignEnum.unsigned,
                TypeOfInvestment = "Securities", Worth = 17141 },
            new Element { Company = "ORACLE CORP", Signed = SignEnum.unsigned,
                TypeOfInvestment = "Assets", Worth = 59372 },
            new Element { Company = "PEPSICO INC", Signed = SignEnum.notAvail,
                TypeOfInvestment = "Assets", Worth = 26516 },
            new Element { Company = "PROCTER & GAMBL", Signed = SignEnum.signed,
                TypeOfInvestment = "Stocks", Worth = 387050 },
            new Element { Company = "QUASLCOMM INC", Signed = SignEnum.unsigned,
                TypeOfInvestment = "Bonds", Worth = 196811 },
            new Element { Company = "UTD TECHS CORP", Signed = SignEnum.signed,
                TypeOfInvestment = "Bonds", Worth = 257429 },
            new Element { Company = "WELLS FARGO-NEW", Signed = SignEnum.unsigned,
                TypeOfInvestment = "Bank Account", Worth = 106600 },
            new Element { Company = "FEDEX CORP", Signed = SignEnum.notAvail,
                TypeOfInvestment = "Stocks", Worth = 103955 },
            new Element { Company = "CVS CAREMARK CP", Signed = SignEnum.notAvail,
                TypeOfInvestment = "Securities", Worth = 171048 },
        };

        string header = "Company".PadLeft(15) + " " +
            "Signed".PadLeft(10) + " " +
            "Type Of Investment".PadLeft(20) + " " +
            "Worth".PadLeft(10);
        Console.WriteLine(header);
        Console.WriteLine(new string('-', header.Length));
        foreach (var e in elements)
        {
            Console.WriteLine(e.Company.PadLeft(15) + " " +
                e.Signed.ToString().PadLeft(10) + " " +
                e.TypeOfInvestment.PadLeft(20) + " " +
                e.Worth.ToString().PadLeft(10));
        }

        Console.WriteLine();

        var query = from e in elements
                                                        group e by e.TypeOfInvestment into eg
                                                        select new GroupedResult
                                                        {
                                                            TypeOfInvestment = eg.Key,
                                                            signed = eg.Where(x => x.Signed == SignEnum.signed).Sum(y => y.Worth),
                                                            unsigned = eg.Where(x => x.Signed == SignEnum.unsigned).Sum(y => y.Worth),
                                                            notAvailable = eg.Where(x => x.Signed == SignEnum.notAvail).Sum(y => y.Worth),
                                                            Sum = eg.Sum(y => y.Worth)
                                                        };

        string header2 = "Type of investment".PadRight(20) + " " +
            "signed".PadLeft(8) + " " +
            "unsigned".PadLeft(8) + " " +
            "notAvailable".PadLeft(13) + " " +
            "Sum";
        Console.WriteLine(header2);
        Console.WriteLine(new string('-', header2.Length));
        foreach (var item in query)
        {
            Console.WriteLine(item.TypeOfInvestment.PadRight(20) + " " +
                item.signed.ToString().PadLeft(8) + " " + 
                item.unsigned.ToString().PadLeft(8) + " " +
                item.notAvailable.ToString().PadLeft(13) + " " +
                item.Sum.ToString()
                );
        }
    }
}

And the result:

        Company     Signed   Type Of Investment      Worth
----------------------------------------------------------
 JPMORGAN CHASE     signed               Stocks      96983
AMER TOWER CORP   unsigned           Securities      17141
    ORACLE CORP   unsigned               Assets      59372
    PEPSICO INC   notAvail               Assets      26516
PROCTER & GAMBL     signed               Stocks     387050
  QUASLCOMM INC   unsigned                Bonds     196811
 UTD TECHS CORP     signed                Bonds     257429
WELLS FARGO-NEW   unsigned         Bank Account     106600
     FEDEX CORP   notAvail               Stocks     103955
CVS CAREMARK CP   notAvail           Securities     171048

Type of investment     signed unsigned  notAvailable Sum
--------------------------------------------------------
Stocks                 484033        0        103955 587988
Securities                  0    17141        171048 188189
Assets                      0    59372         26516 85888
Bonds                  257429   196811             0 454240
Bank Account                0   106600             0 106600

score 0 · Answer 4 · answered May 16 '12 at 11:55

Try this:

var obj = (
    from f in context.assets
    group f by f.typeOfInvestment into gvc
    let lookup = gvc.ToLookup(x => x.signed, x => x.worth)
    select new
    {
        TypeOfInvestment = gvc.Key,
        Signed = lookup["signed"].Sum(),
        Unsigned = lookup["unsigned"].Sum(),
        NotAvailable = lookup["notAvail"].Sum(),
        SumOfWorth = gvc.Select(x => x.worth).Sum(),
    }).ToList();

Linq group by multiple columns and sum them together

4 Answers4