Make a multiplication in a SQL

Question

I have to do a search to return a value , I have to do is the sum of multiplying two fields. I have the following code:

internal double TotalRes(long Id)
{
     double total = 0;
     Reserve rAlias = null;
     var query = Session.QueryOver<Item>();
     query = query.JoinAlias(e => e.Reserve, () => rAlias);
     query = query.Where(() => rAlias.Id == Id);
     query = query.Select(Projections.Sum<Item>(acct => acct.Ammount * acct.Wight));
     object result = query.UnderlyingCriteria.UniqueResult();
     if (result != null)
         total = Convert.ToDouble(result);
     return total;
}

It is giving the following error:

the variable 'acct' type 'tem' is referenced in scope '', but it is not set

How can i return this value?

Answer 1

Try to do something like this in the mapping, using formula.

Map(c => c.total).formula("(select sum(Ammount * Wight) from acct)");

Answer 2

Try this

Item rItem = null;
var query = Session.QueryOver<Item>(() => rItem);
...
query = query.Select(Projections.Sum(
        Projections.SqlFunction(new VarArgsSQLFunction("(", "*", ")"),
        NHibernateUtil.Double,
        Projections.Property(() => rItem.Ammount),
        Projections.Property(() => rItem.Wight))));