Nicolas Massart Nicolas Massart - 1 month ago 5
MySQL Question

How to combine two sql counts from same joined table

another annoying student here!
Today I spend hours trying to combine (select) 2 already joined SQL outputs + the ID of the original table in a single table output. which ultimately resulted in this query:

SELECT * FROM(
SELECT fd1.User_idUser,avg(fd1.caloryIntake)
AS 'workdays'
FROM fact_dailysnapshot fd1
INNER JOIN dim_day dd1 ON dd1.DATE_SK = fd1.DATE_SK
WHERE dd1.weekend_ind = 'N'
GROUP BY fd1.User_idUser
ORDER BY fd1.User_idUser) A,

(SELECT avg(fd1.caloryIntake) AS 'weekend'
FROM fact_dailysnapshot fd1
INNER
JOIN dim_day dd1 ON dd1.DATE_SK = fd1.DATE_SK
WHERE dd1.weekend_ind = 'Y'
GROUP BY fd1.User_idUser
ORDER BY fd1.User_idUser) B;


Which translates into...
output

Now this is a false result, the second column gives an almost constant value for all user entries. I think this must be solved with some kind of EXTRA join but I literally ran out of ideas. Thanks in advance..!

Answer

Your JOIN is missing an ON clause to relate dUser_idUser.

But, the simplest way to write the query uses conditional aggregation:

SELECT fd1.User_idUser,
       avg(case when dd1.weekend_ind = 'N' then fd1.caloryIntake end) as weekday_avg,
       avg(case when dd1.weekend_ind = 'Y' then fd1.caloryIntake end) as weekend_avg
FROM fact_dailysnapshot fd1 INNER JOIN
     dim_day dd1
     ON dd1.DATE_SK = fd1.DATE_SK
GROUP BY fd1.User_idUser 
ORDER BY fd1.User_idUser;

This is one query instead of two.