如果第二个表有多个出现，如何连接表并得到第一个表的总和(how to join tables and get sum of first table if second has multiple occ

如果第二个表有多个出现，如何连接表并得到第一个表的总和(how to join tables and get sum of first table if second has multiple occurences)

我有两个表“temp_user_batches”和“user_activities”我试图找到temp_user_batches表中的用户的user_activities总和。

问题是user_activities的总和乘以temp_user_batches表中用户出现次数的次数。

下面是temp_user_batches表

这是user_activities表

它应该给time_spent列649 + 364 = 1013总和，但它给予2016

我的查询是：

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

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

This is user_activities table

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

最满意答案

您可以使用包含表temp_user_batches的user_id, activity_goal的DISTINCT对的派生表：

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

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