I need to do a SUM within Group BY in Lambda

Question

I need to do a SUM within Group BY in Lambda

Navigation

#1 by (4 votes)
#2 by (2 votes)

1

I'm trying to create a summary report where it consists of doing GROUP BY with SUM , I want to know how I could do this in C # with lambda or hql (Nhibernate), I was able to do a GROUP BY, but I can not puts SUM, an example of what I want would be like this:

SELECT NumCarroId, 
      SUM(Litro) LitroTotal, 
      SUM(TotalGasto)TotalConsumido 
FROM Abastecimento 
GROUP BY NumCarroId

But how could you do this in HQL or LINQ?

c# nhibernate hql

asked by anonymous 02.02.2018 / 17:28

2 answers

2

You can use LINQ to do group by like this:

var resultado = from x in suaLista
                group x by NumCarroId into g
                select new 
                {
                        NumeroCarroId = g.NumCarroId,
                        LitroTotal = g.Sum(a => a.Litro),
                        TotalConsumido = g.Sum(b => b.TotalGasto)
                };

Where suaLista is your object with the result you want to group, from the Abastecimento

02.02.2018 / 17:43

Show only the last word of a string Date format in fraction

score 4 · Accepted Answer

The GroupBy method returns a grouping structure.

This structure contains all the data of the grouping in question and also the key ( Key ) of it - this key is responsible for the grouping. For example, in your case, you want to group the items with the NumeroCarroId property, so it is this property that you pass as a parameter to the GroupBy method and, as a result, ( Key property) of this collation structure that is returned.

Then you can work on this structure and use the Count() method to return the number of elements of each grouping, or Sum() to add up the values of some property and so on.

Let's look at an example, imagine that you have the following structure in your table

NumeroCarroId  | Litro  | TotalGasto
       1       |   10   |     50
       1       |   15   |     75
       2       |   10   |     50
       2       |   30   |     150
       2       |   05   |     25

When doing this

tabela.GroupBy(x => x.NumeroCarroId)

The result will be a structure like this

[
  {
    Key: 1, 
    _Items: [ 
      { NumeroCarroId: 1, Litro: 10, TotalGasto: 50 },
      { NumeroCarroId: 1, Litro: 15, TotalGasto: 75 } 
    ]
  }
  {
    Key: 2,
    _Items: [
      { NumeroCarroId: 2, Litro: 10, TotalGasto: 50 },
      { NumeroCarroId: 2, Litro: 30, TotalGasto: 150 },
      { NumeroCarroId: 2, Litro: 05, TotalGasto: 25 },
    ]
  }
}

Note that this is a simplified illustration to simulate the structure returned by the GroupBy method.

Each item in this structure is gp in Select below

tabela.GroupBy(x => x.NumeroCarroId)
      .Select(gp => new 
                    { 
                        NumeroCarroId = gp.Key,
                        LitroTotal = gp.Sum(c => c.Litro),
                        TotalConsumido = gp.Sum(c => c.TotalGasto)
                    });

From here, you can use the ToList method to get all items from it, the Sum method to add a given property, the Count method to count the items in each group, and a few other things.

The code, using the method syntax (what you are calling lambda) would look like this:

var resultado = consulta.GroupBy(c => c.NumCarroId)
                        .Select(gp => new 
                                      { 
                                          NumeroCarroId = gp.Key,
                                          LitroTotal = gp.Sum(c => c.Litro),
                                          TotalConsumido = gp.Sum(c => c.TotalGasto)
                                      });