CODEWITHSUNDEEP
mysqlsqloracle-databasephpmyadmin
Valeria Kaya
Valeria Kaya

Reputation: 131

SUM of 2 separate tables

I have 2 tables below

Table 1

ID  TYPE MONTH QTY
1   I6    1     5
2   I5    2     5
3   I4    1     5
4   I6    2     5

Table 2

ID  TYPE MONTH QTY
1   I6    1     5
2   I5    1     5
3   I5    2     5

Desired Output (SUM of both tables)

TYPE MONTH1QTY MONTH2QTY
IP4    5          0
IP6    10         5
IP5    5          10

How can I achieve this?

Thanks in advance.

Upvotes: 1

Views: 50

Answers (1)

MatBailie
MatBailie

Reputation: 86735

Because the two tables are the same format it's trivial to bring both tables together using UNION ALL (NOT just UNION, as that de-duplicates the data sets).

Then you can use SUM(CASE WHEN) to pivot your data.

SELECT
  type,
  SUM(CASE WHEN month = 1 THEN qty END)   AS month_1_qty,
  SUM(CASE WHEN month = 2 THEN qty END)   AS month_2_qty
FROM
(
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
)
   unified_table
GROUP BY
    type

Note, that without dynamic SQL you have to hard code which months you want to query. (You can't get an extra column 'magically' appear simply because a new month appears in the data.)

In such a case that you don't know which months you want to query, keep the data in a normalised form, then pivot it in your application / presentation layer.

SELECT
  type,
  month,
  SUM(qty)   AS qty
FROM
(
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
)
   unified_table
GROUP BY
    type,
    month

In the event that you have different fields in each table, just be a little more specific in the UNION ALL.

(
    SELECT type, month, qty FROM table1
    UNION ALL
    SELECT type, month, qty FROM table2
)

Upvotes: 1

Related Questions