Sqlite get the average of combined two tables columns

Question

I have the following problem which i'm not sure how to approach a solution. I have the following db schema:

I want to get the median of both columns of both tables, filtered by userid, year and month. Doing this with two separate queries, i would do the following:

select avg(heartRate), avg (bodyWater) from table1 where user1d = 1234 and month = 1 and year = 2018

select avg(stepCount), avg (distance), avg (calories), avg (sleep) from table2 where user1d = 1234 and month = 1 and year = 2018

Then i would merge the result in code.

I want to accomplish the same with a single query, but there is a catch. If one table return no rows, i want to return default values of 0, in this particular case. I cannot use ifnull clause, because it only works if there is any row returned, not if no rows are found for one table. So my question is: Is this a valid approach i should use, or this can't be done and i should stick to two queries and join the value in code?

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

Based upon the following tables (extended to cater for no row handling) :-

The query :-

SELECT avghr, avgbw, avgsc, avgdist, avgc, avgslp FROM 
    (SELECT avg(heartrate) AS avghr, avg(bodywater) AS avgbw
        FROM tbl1 where userid = 1234 AND year = 2018 AND month = 1),
    (SELECT avg(stepcount) AS avgsc, avg(distance) AS avgdist, avg(calories) AS avgc, avg(sleep) AS avgslp 
        FROM tbl2 where userid = 1234 AND year = 2018 AND month = 1)

results in :-

For month 2 :-

SELECT avghr, avgbw, avgsc, avgdist, avgc, avgslp FROM 
    (SELECT avg(heartrate) AS avghr, avg(bodywater) AS avgbw
        FROM tbl1 where userid = 1234 AND year = 2018 AND month = 2),
    (SELECT avg(stepcount) AS avgsc, avg(distance) AS avgdist, avg(calories) AS avgc, avg(sleep) AS avgslp 
        FROM tbl2 where userid = 1234 AND year = 2018 AND month = 2)

results in :-

For month 3 :-

SELECT avghr, avgbw, avgsc, avgdist, avgc, avgslp FROM 
    (SELECT avg(heartrate) AS avghr, avg(bodywater) AS avgbw
        FROM tbl1 where userid = 1234 AND year = 2018 AND month = 3),
    (SELECT avg(stepcount) AS avgsc, avg(distance) AS avgdist, avg(calories) AS avgc, avg(sleep) AS avgslp 
        FROM tbl2 where userid = 1234 AND year = 2018 AND month = 3)

results in :-

If you wanted 0's instead of nulls then the following, longwinded, SQL could be used :-

SELECT avghr, avgbw, avgsc, avgdist, avgc, avgslp, userid, year, month FROM 
    (SELECT 
        CASE 
            WHEN avg(heartrate) IS NULL THEN 0
            ELSE avg(heartrate)
        END AS avghr,
        CASE
            WHEN avg(bodywater) IS NULL THEN 0
            ELSE avg(bodywater)
        END AS avgbw
        FROM tbl1 where userid = 1234 AND year = 2018 AND month = 2),
    (SELECT
        CASE
            WHEN avg(stepcount) IS NULL THEN 0
            ELSE avg(stepcount)
        END AS avgsc,
        CASE
            WHEN avg(distance) IS NULL THEN 0
            ELSE avg(distance)
        END AS avgdist,
        CASE
            WHEN avg(calories) IS NULL THEN 0
            ELSE avg(calories)
        END AS avgc,
        CASE 
            WHEN avg(sleep) IS NULL THEN 0
            ELSE avg(sleep)
        END AS avgslp,
        userid, year, month 
            --???? not really needed 
            -- (if not used remove cols userid, year, month from primary/outer select)
            -- as well as from here.
        FROM tbl2 where userid = 1234 AND year = 2018 AND month = 2)

This would result in (for month 2, note SQL was saved to a view hence VIEW) :-

Notes

Table names have been changed for convenience instead of table1, tbl1 was used and likewise instead of table2, tbl2 was used.
column name userid was used instead of user1d
The resultant cursor would have column names as per the result (avghr, avgbw etc.)
The screen shots are from SQLite Manager, data is colour coded according to column type, as per Red = NULL, Green = INTEGER, DARKER GREEN=REAL, Blue = TEXT

Sqlite get the average of combined two tables columns

1 Answers1

Notes