How to return a SUM() in a SELECT query

Hello, Is there a way to return a SUM in a select query? I know that i can execute Calculate() method on a UOW, but i need to have that value as a field in a select return (e.g.: SELECT f1,...,SUM(fk) AS m,...fn FROM...), so by using a Query or a QueryExpression. I must note that i'm using .NET 2.0, so i cant use Linq. Thanks.

You can do this using the PathExpression.Function method e.g.

Entity.Attribute("Views").Function("SUM")

But if you want to receive records rather than a scalar then you will need to use a projection (IUnitOfWork.Project).  And in this case you may run into database errors where it complains about trying to combine aggregated with non-aggregate values (I didn't investigate this in detail though).

Thanks for your answer, Ivan! Suppose i use a projection. In this case, i'll have to add the field names to be returned to the Projection collection of a Query. Who will i add the SUM() calculated field to this collection? Under what name?

You would add the SUM() as a QueryExpression e.g.

query.Projection.Add(Entity.Attribute("Price").Function("SUM"));

You don't need to specify a name -- you will retrieve it from the returned IDataReader by position.

However once again I do need to mention that I've run into difficulties trying to return multiple rows this way (e.g. when you're summing over an association).  So depending on your scenario you may need a bit of trial and error, and you may end up needing to fall back on a view or stored procedure instead.

Thanks again Ivan for your answer! One more question i want to ask you. Suppose SUM() takes a form like this: SELECT SUM(f1 * f2) FROM ...., where f1 and f2 are two fields from the table. More exactly, i want the sum to be calculated based on arithmetic operation of two fields. Now, i know that i can do something like: UnitOfWork.Calculate("SUM", "f1*f2") but again, this will return a scalar which i dont need. If i follow your example using: query.Projection.Add(Entity.Attribute("f1 * f2").Function("SUM")); i will get an error, saying that "Could not find field [f1 * f2] on model [Entity]". How can i solve this issue? Thanks!

The core API isn't really designed for this sort of thing -- LINQ is much nicer for this -- but you can still do it using the following rather verbose projection:

q.Projection.Add(Entity.Attribute("f1").Function("*", Entity.Attribute("f2")).Function("SUM"));

The key thing to realise is that you can't just embed functions or operators into the Entity.Attribute literal string.  Entity.Attribute *always* tries to map its argument to an entity property (and thereby to a column): it *never* tries to parse its argument into smaller pieces.  To apply a function to a column -- and we treat operators like * as functions -- you must always use the Function method.

So in this case we are getting the f1 column:

Entity.Attribute("f1")

applying the * function/operator with its argument being the f2 column:

.Function("*", Entity.Attribute("f2"))

then applying the SUM function to the resulting expression:

.Function("SUM")

You should be able to follow a similar pattern if you need to build up other complex queries.

Thanks a lot for your answer, Ivan!