Lannister Lannister - 4 months ago 8
SQL Question

how to join tables and get sum of first table if second has multiple occurences

I have two tables "temp_user_batches" and "user_activities" i am trying to find sum of user_activities for users present in temp_user_batches table.

problem is sum of user_activities is getting multiplied by number of times in ratio of occurences of user in temp_user_batches table.

Below is temp_user_batches table

enter image description here

This is user_activities table

enter image description here

it is supposed to give sum of time_spent column

649 + 364
=
1013
but instead its giving
2016


my query is:

SELECT temp_user_batches.user_id as user_id,
temp_user_batches.activity_goal as goal,
DATE_SUB(CURDATE(), INTERVAL 7 day) as min_activity_date,
CURDATE() as max_activity_date,
(sum(user_activities.time_spent)/60) as total_time_spent
FROM temp_user_batches
INNER JOIN user_activities
ON temp_user_batches.user_id = user_activities.user_id
WHERE activity_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 day) AND CURDATE()
group by user_id, goal, max_activity_date, min_activity_date

Answer

You can use a derived table that contains the DISTINCT pairs of user_id, activity_goal from table temp_user_batches:

SELECT t1.user_id as user_id, 
       t2.activity_goal as goal,
       DATE_SUB(CURDATE(), INTERVAL 7 day) as min_activity_date, 
       CURDATE() as max_activity_date,
       (sum(t2.time_spent)/60) as total_time_spent
FROM (
   SELECT DISTINCT user_id, activity_goal
   FROM temp_user_batches) AS t1
INNER JOIN user_activities AS t2 ON t1.user_id = t2.user_id
WHERE activity_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 day) AND CURDATE()
group by user_id, goal, max_activity_date, min_activity_date
Comments