How to add rows in multiple tables together

[lethal0r]

How would i add the rows in table 1 and 2 up to get the output shown below? I would also need to order the results, and some fruits may not be in each table.

table 1
-------
apples 5
bananas 2
oranges 3

table 2
-------
apples 3
bananas 1
oranges 0

output
------
apples 8
bananas 3
oranges 3

Thanks :)
Tom

[ChadSEO]

lethal0r,

The tricky part of this is when you don't have fruits listed in one of the tables. If you know that all the fruits are listed in table1, but might not be in table2, you could use this query:

SELECT table1.name, (ifnull(table1.cnt,0) + ifnull(table2.cnt,0)) as cnt
FROM table1 left join table2 on table1.name = table2.name
GROUP BY table1.name

Otherwise, the following query should get you what you want:

SELECT table1.name, (ifnull(table1.cnt,0) + ifnull(table2.cnt,0)) as cnt
FROM table1 left join table2 on table1.name = table2.name
GROUP BY table1.name
union
SELECT table2.name, (ifnull(table1.cnt,0) + ifnull(table2.cnt,0)) as cnt
FROM table1 right join table2 on table1.name = table2.name
GROUP BY table2.name

This assumes you're using MySQL - I'm not sure what databases support the ifnull syntax, if you find that yours does not, let me know what it is and I'll try to figure out what you need.

Chad

[coopster]

It would be best to use a

UNION ALL

in this case.

The default behavior for

UNION

is that duplicate rows are removed from the result. A

DISTINCT

keyword has no effect other than the default because it also specifies duplicate-row removal. With the

ALL

keyword, duplicate-row removal does not occur and the result includes all matching rows from all the

SELECT

statements. This becomes very very important when adding values because if two fruits existed in both tables with the same exact quantity, one of the rows would be removed from the result set as it would be recognized as a duplicate. And now the total would return only half of what it is supposed to return.

SELECT 
 fruit, 
 SUM(qty) AS total 
 FROM 
  (SELECT fruit, qty FROM fruit1) 
  UNION ALL  
  (SELECT fruit, qty FROM fruit2) 
  AS fruitAll 
 GROUP BY fruit 
;

[lethal0r]

thanks for the replies guys. I used your version coopster as I need to pop in a new table for each month that passes, and your sql is nice and short.

ive spent about 6 hours on this today, why didnt i just wait to see what was posted here! :(

[coopster]

Because you learn more when you struggle with something ;)

In the process of getting to the end resolution you learn all kinds of other stuff that may or may not work but at least you are picking up knowledge along the way to help make better decisions on future tasks.