CODEWITHSUNDEEP
mysqlselectunion
Zorann
Zorann

Reputation: 397

MySQL sum of values from different tables with different number of columns

I'm basing on this question: mySQL sum of two values in 2 different tables

In my case, the tables does look like this:

table_1
reference    quantity   name
TS00001      235        AAA
TS00002      400        BBB
TS00003      850        CCC
...

table_2
reference    quantity
TS00001      670
TS00002      210
TS00003      150
...

I'd like to get sum of quantity from both tables for given reference, but at the same time I'd like to get the name for the reference from table_1.

The provided answer:

SELECT reference, SUM(quantity) AS total_quantity
FROM (
  SELECT reference, quantity
  FROM table_1

  UNION ALL

  SELECT reference, quantity
  FROM table_2) AS t
GROUP BY reference

does the trick, if I want to simply get sum of values. I tried to make modification like this to get the name as well:

SELECT reference, name, SUM(quantity) AS total_quantity
FROM (
  SELECT reference, name, quantity
  FROM table_1

  UNION ALL

  SELECT reference, quantity
  FROM table_2) AS t
GROUP BY reference

But it does not work, it does return The used SELECT statements have a different number of columns.

Expected result should look like this:

reference    total_quantity   name
TS00001      905              AAA
TS00002      610              BBB
TS00003      1000             CCC
...

Upvotes: 0

Views: 85

Answers (2)

FanoFN
FanoFN

Reputation: 7114

Here's a different approach using JOIN and instead of SUM(), you simply just add the value:

SELECT table_1.reference, 
       table_1.name AS final_name, 
       table_1.quantity + table_2.quantity AS total_quantity
FROM   table_1 
JOIN   table_2 
ON     table_1.reference=table_2.reference;

A fiddle for demo : https://www.db-fiddle.com/f/9541k7FCfz5JvqggsoJ12S/2

Upvotes: 0

Zorann
Zorann

Reputation: 397

While writing the question I found solution. We need to artificially input the missing column to the table that does not have it to match the numbers of columns from both tables.

Since we don't want to give there any additional data, the trick is to add NULL as name in the table that does not have column name.

SELECT reference, MAX(name) AS final_name, SUM(quantity) AS total_quantity
FROM (
  SELECT reference, name, quantity
  FROM table_1

  UNION ALL

  SELECT reference, NULL as name, quantity
  FROM table_2) AS t
GROUP BY reference

This does return the expected output without errors.

Edit: Changed MAX(name) AS final_name according to @ysth comment.

Upvotes: 1

Related Questions