SQLite arithmetic between columns of different tables

Question

I have a statement like so:

SELECT f.column1, 0.9*sum(s.column2) FROM first f, second s WHERE f.colum1 = f.column1*0.9*sum(s.colum2)

Basically what I want to do is multiply the value from the 'first' table in the column1, by 0.9*sum(s.colum2) i.e the sum of the values in column2 of the second table multiplied by 0.9.

I tested the sum*0.9 part on its own and it works but it doesn't work after the WHERE condition. Could anyone tell me the proper way to do this? Right now the error I'm getting is:

could not prepare statement (1 misuse of aggregate: sum())

score 1 · Accepted Answer · edited Jul 03 '15 at 14:41

1

The answer for you can be using HAVING instead of WHERE:

WITH f AS (SELECT 12 AS column1 FROM dual),
     s AS (SELECT 33 AS column2 FROM dual)
SELECT f.column1, 0.9*SUM(s.column2) FROM f,s
HAVING f.column1 = f.column1*0.9*SUM(s.column2);

But what is the idea of statement WHERE f.colum1 = f.column1*0.9*sum(s.colum2)?

It compares the values in the row in the first colunm and compare them with the same value multiplied by 0.9*SUM(s.colum2). The query will therefore return nothing if 0.9*SUM(s.colum2) != 1 and all rows if 0.9*SUM(s.colum2) == 1.

edited Jul 03 '15 at 14:41

Liam

27,717
28
128
190

answered Apr 21 '14 at 19:16

Evgeniy

540
4
17

Thanks for your answer, I figured it out in the end and I have posted what I did. Yeah I wasn't really sure what I was doing with the WHERE but I ended up doing it differently – Mohd Apr 21 '14 at 19:30

score 0 · Answer 2 · answered Apr 21 '14 at 19:29

I figured it out eventually, in case anyone is interested, I changed the code to this:

SELECT f.column1*0.9*sum(s.column2) FROM first f, second s

Instead of doing the inter-column arithmetic in the WHERE condition, I simply did it when listing the column names after the SELECT keyword and it works fine

SQLite arithmetic between columns of different tables

2 Answers2