I've been searching everywhere for this but no cigar. Smoke is starting to come out of my ears. please help
How do you sum two columns in two tables and group by userid?
Have two tables.
Recipe Table
recipeid userid recipe_num_views
Meals Table
mealsid userid meal_num_views
Goal is to sum the num views in both tables and group by userid
so for example
Recipe Table
1 3 4
2 4 6
Meal Table
1 3 2
2 4 5
select sum(recipe views)+sum(meal views)
WHERE recipe.userid=meals.userid GROUP BY userid
should give
userid=3 , sum=6
userid=4, sum=11
this gives a much bigger number.
解决方案SELECT recipe.userid, sum(recipe_num_views+meal_num_views)
FROM Recipe JOIN Meals ON recipe.userid=meals.userid
GROUP BY recipe.userid
EDIT:
OK, from your comments, I understand that when you have for user 3: 4 recipes & 3 meals you will get the sum of the combination of all these rows => sum(recipes)*3 + sum(meals)*4
Try this query instead:
select r.userid, (sum_recipe + sum_meal) sum_all
FROM
(select userid, sum(recipe_num_views) sum_recipe
FROM Recipe
GROUP BY userid) r
JOIN (
select userid, sum(meal_num_views) sum_meal
FROM Meals
GROUP BY userid) m ON r.userid = m.userid