dev_054 dev_054 - 2 months ago 10
SQL Question

Group By + Left Outer Join In Sqlite

I'm trying to

join
2 tables (even if there's no match for the 2nd
table
I want to bring the results).

So I thought I can solve that problem using a
LEFT OUTER JOIN
, but for some reason I'm not able to do that.

Here's the schema:

entry_types
table:

ID NAME
---------- ----------
1 entry_type1
2 entry_type2


entries
table:

ID VALUE ENTRY_TYPE_ID DATE
---------- ---------- -------------- ----------
1 55.5 1 2016-09-18T17:46:27.398Z
2 84.21 2 2016-09-18T18:41:54.142Z
3 144.5 2 2016-09-19T01:13:51.099Z
4 150.7 1 2016-07-17T19:28:12.026Z


Looking to the schema above we can imply that I have both
entry_types
ocurring in September, but in July I have only one
entry_type
.

So, what I want?


  • I want to retrieve always the two
    entry_types
    , and, of course, set 0 to the inexistent
    entry_type
    (if there's one).



The following
query
that I'm trying is the following:

SELECT et.name as entry_type,
SUM(CASE WHEN en.value IS NULL THEN 0 ELSE en.value END) as total
FROM entries en
LEFT OUTER JOIN entry_types et
ON en.entry_type_id = et.id
WHERE STRFTIME('%m', en.date) = 'SOME MONTH'
GROUP BY en.entry_type_id


The expected result:

If I search by 'September':

NAME TOTAL
---------- ----------
entry_type1 55.5
entry_type2 228.71


If I search by 'July':

NAME TOTAL
---------- ----------
entry_type1 150.7
entry_type2 0


Thanks in advance. Any help will be appreciated.

Answer
SELECT t1.name,
       COALESCE(t2.value, 0)
FROM entry_types t1
LEFT JOIN
(
    SELECT entry_type_id, SUM(value) AS value
    FROM entries
    WHERE STRFTIME('%m', date) = 'SOME MONTH' 
    GROUP BY entry_type_id
) t2
    ON t1.id = t2.entry_type_id
Comments