I need to do a SUM inside Group BY in Lambda

Asked

Viewed 2,261 times

3

I’m trying to create a summary report where it consists of making a GROUP BY with SUM, i want to know how I could do it in c# with lambda or hql(Nhibernate), I managed to do a GROUP BY, but I can’t put the 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 it be done in HQL or LINQ?

c# lambda-expressions nhibernate hql

  • Ricardo’s answer fits your question?

  • Yes and no, I wanted to know how to put in Lambda in case you don’t get in LINQ, I put in the code but I don’t have much understanding in LINQ

  • I put an answer using method syntax, but at the end of the day, what changes if you use one syntax or another?

  • @Guillermo decided to come to the Entity? :)

2 answers

8


The method GroupBy returns a grouping structure.

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

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

Let’s go to an illustrated example, imagine 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

In 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 simple illustration to simulate the structure returned by the method GroupBy.

Each item of this structure is gp in the Select down 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 it, you can use the method ToList to get all of her items, the method Sum to sum a given property, the method Count to count the items of each grouping and some other things.

The code, using method syntax (what you’re 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)
                                      });

3

You can use LINQ to do the group by thus:

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 table Abastecimento

Browser other questions tagged c# lambda-expressions nhibernate hql

You are not signed in. Login or sign up in order to post.