我有两个表“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_dateI 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_dateYou 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更多推荐
发布评论