Can SQL SUM() function take an expression as argument?

Question

I'm using SQLite database and I'm wondering whether I'm allowed to write queries as follows:

SELECT SUM(column1 * column2)
FROM my_table;

I googled but references say that SUM function is has the following format:

SUM([DISTINCT|ALL] column)

And my question is: does column mean actually column or does it allow expressions (like above) too?

score 4 · Answer 1 · answered May 14 '12 at 16:58

4

You can always use a table expression:

SELECT SUM(Calc)
FROM (
SELECT Column1 * Column2 AS 'Calc'
FROM My_Table) t

I don't have SQLite but checking the docs indicates this should work fine.

answered May 14 '12 at 16:58

JNK

Thanks for quick answer! Your variant looks fine but assume situation when I have `GROUP BY` clause, e.g. `SELECT SUM(column1*column2) FROM my_table GROUP_BY column3`. I don't see any way to convert such a query to your variant. – tonytony May 14 '12 at 17:04
@tonytony It's a moot point since apparently your original code should work fine. – JNK May 14 '12 at 17:07

score 3 · Accepted Answer · answered May 14 '12 at 17:01

3

Yes, you can use an expression like the one you mentioned, if the datatype of both columns allow it.

answered May 14 '12 at 17:01

aF.

2 Answers2