Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sum Two Columns in Two Mysql Tables

Tags:

mysql

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.

like image 433
user813801 Avatar asked Jun 24 '11 09:06

user813801


People also ask

How do I sum two columns in MySQL?

MySQL SUM() function retrieves the sum value of an expression which is made up of more than one columns. The above MySQL statement returns the sum of multiplication of 'receive_qty' and 'purch_price' from purchase table for each group of category ('cate_id') .

How Sum values from another table in MySQL?

You can use the SUM() function in a SELECT with JOIN clause to calculate the sum of values in a table based on a condition specified by the values in another table.

How do I sum multiple columns in SQL?

The SQL AGGREGATE SUM() function returns the SUM of all selected column. Applies to all values. Return the SUM of unique values.

How do I match two columns in MySQL?

Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.


2 Answers

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
like image 156
manji Avatar answered Oct 04 '22 22:10

manji


If you're selecting from 2 tables you need to join them. Otherwise MySQL will not know how to link up the two tables.

select sum(recipe_num_views + meal_num_views) 
from recipe r
inner join meals m ON (r.user_id = m.user_id)
group by m.user_id

See:
http://dev.mysql.com/doc/refman/5.5/en/join.html
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

like image 22
Johan Avatar answered Oct 04 '22 21:10

Johan