How to use the SUM aggregation function in a NHIBERNATE query?

Question

My consultation consists in knowing the NumCarroId, TotalGasto and VlrUnit, in Sql I managed to do that but when I play in HQL returns the following error:

Application of GROUP BY next to the SUM to make a Tolist, to show me a list already added, but there are many errors

Personal if anyone can help, for now I have not found any answer that will work

Error that returns:

string hql = "select a.NumCarro.Id, sum(a.Totalspent) spent from Sourcing to WHERE a.Dtfuelled Between :dateInitial AND :dataFinal GROUP BY a.NumCarro.Id";

Code

public IList<Abastecimento> Resumo(DateTime dataInicio, DateTime dataFinal)
{
    string hql = "SELECT NumCarroId, DtAbastecido FROM Abastecimento a WHERE a.DtAbastecido Between :dataInicial AND :dataFinal GROUP BY a.NumCarroId";
    IQuery query = session.CreateQuery(hql)
    .SetParameter("dataInicial", dataInicio)
    .SetParameter("dataFinal", dataFinal);
    return query.List<Abastecimento>();
}

Table Supply

CREATE TABLE [dbo].[Abastecimento] (
    [Id]            INT             IDENTITY (1, 1) NOT NULL,
    [DtAbastecido]  DATETIME        NULL,
    [Litro]         INT             NULL,
    [VlrUnit]       DECIMAL (18, 2) NULL,
    [TotalGasto]    DECIMAL (18, 2) NULL,
    [AutorId]       INT             NULL,
    [NumCarroId]    INT             NULL,
    [Km]            INT             NULL,
    [NomeProdutoId] INT             NULL,
    [Km_Andado]     INT             NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK84286500787E6DCB] FOREIGN KEY ([AutorId]) REFERENCES [dbo].[Usuario] ([Id]),
    CONSTRAINT [FK842865002F58EAD8] FOREIGN KEY ([NumCarroId]) REFERENCES [dbo].[Veiculo] ([Id]),
    CONSTRAINT [FK8428650023D2F9D9] FOREIGN KEY ([NomeProdutoId]) REFERENCES [dbo].[Compra] ([Id])
);

Model Abastecimento

 public class Abastecimento {
 public virtual int Id {
  get;
  set;
 }
 [Required]
 public virtual int Litro {
  get;
  set;
 }
 public virtual DateTime ? DtAbastecido {
  get;
  set;
 }
 public virtual decimal VlrUnit {
  get;
  set;
 }
 public virtual int Km {
  get;
  set;
 }
 public virtual decimal TotalGasto {
  get;
  set;
 }
 public virtual int Km_Andado {
  get;
  set;
 }
 public virtual Usuario Autor {
  get;
  set;
 }
 public virtual Compra NomeProduto {
  get;
  set;
 }
 public virtual Veiculo NumCarro {
  get;
  set;
 }
}
}

Answer 1

For your mistake, that question: How to resolve No data type for Node error in Hibernate, says:

An HQL Query must contain the object properties and not the fields of your table structure.

In SQL:

select numCarroId, sum(valorGasto) gastos from Abastecimento
where dataAbastecimento between '2017-05-21' and '2017-11-13'
group by numCarroId

Sqlfiddle

You gave it to me via chat Model Abastecimento thus:

public class Abastecimento {
 public virtual int Id {
  get;
  set;
 }
 [Required]
 public virtual int Litro {
  get;
  set;
 }
 public virtual DateTime ? DtAbastecido {
  get;
  set;
 }
 public virtual decimal VlrUnit {
  get;
  set;
 }
 public virtual int Km {
  get;
  set;
 }
 public virtual decimal TotalGasto {
  get;
  set;
 }
 public virtual int Km_Andado {
  get;
  set;
 }
 public virtual Usuario Autor {
  get;
  set;
 }
 public virtual Compra NomeProduto {
  get;
  set;
 }
 public virtual Veiculo NumCarro {
  get;
  set;
 }
}
}

Modify your HQL to:

    public IList < Abastecimento > Resumo(DateTime dataInicio, DateTime dataFinal) {
  string hql = "select a.NumCarro.Id, sum(a.TotalGasto) gastos from Abastecimento a WHERE a.DtAbastecido Between :dataInicial AND :dataFinal GROUP BY a.NumCarro.Id";
  IQuery query = session.CreateQuery(hql)
   .SetParameter("dataInicial", dataInicio)
   .SetParameter("dataFinal", dataFinal);
  return query.List < Abastecimento > ();
 }

Answer 2

I put this next line of code in the controller, and I was able to add Group By, but I still can’t do SUM

var teste = consulta.Where(i => i.DtAbastecido >= dataInicio && i.DtAbastecido <= dataFinal)
                    .GroupBy(x => new { x.NumCarro.NCarro})
                    .Select(x => x.First())
                    .OrderBy(x => x.NumCarro.NCarro);