CODEWITHSUNDEEP
sqlsqlite
dev_054
dev_054

Reputation: 3718

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?

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.

Upvotes: 2

Views: 818

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

I think we can do that without using sub query

SELECT et.id,et.name AS entry_type, 
   CASE WHEN IFNULL(SUM(en.value), '') = '' THEN 0 ELSE  SUM(en.value) AS total 
FROM entry_types et 
LEFT JOIN entries en ON en.entry_type_id = et.id
    AND STRFTIME('%m', en.date) = 'SOME MONTH' 
GROUP BY et.id,et.name

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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

Upvotes: 1

Related Questions