SQL my ISNULL(SUM(FOOBAR),0) query does not work

Question

I am absoltetly new to SQL and created my first three queries

The first without ISNULL provides a result, but there are several columns with NULL values for the sum.

Than I added ISNULL for the first column and it worked, so I add it to the other columns and receive the following error message:

SQL-Fehler [1]: [SQLITE_ERROR] SQL error or missing database (near "ISNULL": syntax error)

I removed the additional ISNULL and even the command with only one ISNULL is not working anymore.

I can't find the error. My request looks like this:

SELECT 
    TABLE1.WORK, PARTNER_ID, ISNULL(SUM(VIEWS),0), ISNULL(SUM(APPLICATION_VIEWS),0), ISNULL(SUM(APPLICATIONS),0)
FROM 
    TABLE1
LEFT JOIN TABLE2
    ON TABLE2.WORK= TABLE1.WORK
LEFT JOIN TABLE3
    ON TABLE2.CATEGORY_ID = TABLE3.ID
LEFT JOIN TABLE4
    ON TABLE1.SOURCE_ID = TABLE4.ID
LEFT JOIN TABLE5
    ON TABLE1.WORK = TABLE5.WORK
WHERE (TABLE3.Category='Foo'
    OR TABLE3.Category='Bar')
    AND TABLE4.NAME='phone' 
GROUP BY
    TABLE1.WORK

Thank you Ozgur, I read tht the order is nearly the same, only if the table1.work value does not exists your suggestion will return NULL and the other will return 0 — , Nov 15 '20 at 11:59

Gordon Linoff · Answer 1 · 2020-11-15T12:37:35.280

Try COALESCE(). This is the standard SQL function to replace a NULL value with another value:

SELECT TABLE1.WORK, PARTNER_ID,
       COALESCE(SUM(VIEWS), 0), 
       COALESCE(SUM(APPLICATION_VIEWS), 0), 
       COALESCE(SUM(APPLICATIONS), 0)

You can also simplify your WHERE clause to:

WHERE (TABLE3.Category IN ('Foo', Bar') AND
      TABLE4.NAME = 'phone'

And finally, after doing changes to make the code run, I don't think it will calculate the sums correctly. I suspect you are joining over different dimensions and getting a Cartesian product.

If that is the case, ask a new question with sample data, desired results, and an explanation of the logic you want to implement. A db/sql fiddle of some sort can also be helpful.

SQL my ISNULL(SUM(FOOBAR),0) query does not work

1 Answers1