Sum and count of records

Question

I have a table with some records:

Col1	Col2	Col3	Col4
1	Row1	99	152
1	Row2	99	20
5	Row3	98	34
1	Row4	120	18
7	Row5	27	74

I need to get the sum of Col4 values where Col1 = 1, sum of Col4 values where Col3 = 99 and Col1 = 1, sum of Col4 values where Col3 <> 99 and Col1 = 1, total count of records where Col1 = 1, count of records where Col3 = 99 and Col1 = 1, count of records where Col3 <> 99 and Col1 = 1 (there is a possibility that there will be no records that meet the criteria).

My SQLite statement looks like that:

    query.SQL.Text:= 'SELECT IFNULL(sum(Col4), 0), '+
                     'IFNULL(sum(case when Col3 = 99 then Col4 else 0 end), 0), ' +
                     'IFNULL(sum(case when Col3 <> 99 then Col4 else 0 end), 0), ' +
                     'IFNULL(count(*), 0), ' +
                     'IFNULL(sum(case Col3 = 99 then 1 else 0 end), 0), ' +
                     'IFNULL(sum(case Col3 <> 99 then 1 else 0 end), 0) ' +
                     'FROM myTable WHERE Col1 = :_Col1';

Is there a way to simplify it?

Answer 1

SQLite evaluates boolean expressions to 1 (true) or 0 (false), so a CASE expression like:

case when Col3 = 99 then 1 else 0 end

can be simplified to:

Col3 = 99

Also, the ELSE part in a CASE expression like:

CASE WHEN Col3 = 99 THEN Col4 ELSE 0 END 

is not needed because later you use IFNULL() to return 0 in case it returns NULL.

Finally, COUNT(*) never returns NULL, so IFNULL() is not needed in this case.

Simplify your code to this:

SELECT IFNULL(SUM(Col4), 0),
       IFNULL(SUM(CASE WHEN Col3 = 99 THEN Col4 END), 0),
       IFNULL(SUM(CASE WHEN Col3 <> 99 THEN Col4 END), 0),
       COUNT(*),
       IFNULL(SUM(Col3 = 99), 0),
       IFNULL(SUM(Col3 <> 99), 0)
FROM myTable 
WHERE Col1 = 1;

See the demo.