1

Below is my sample database. I've been trying to get the sum of every code. I will attach my code below

+----+-----------+----------+-------+
| AMOUNT1 | CODE1 | AMOUNT2 | CODE2 |
+---------+-----------+-------------+
|  500    |  023  |    5    |  020  |
|  9203   |  021  |    20   |  021  |
|  200    |  020  |    50   |  023  |
|  50     |  023  |    56   |  023  |
|  100    |  022  |    87   |  022  |
+---------+-------+---------+-------+

SELECT code1, code2
  (SUM(amount1)+ SUM(amount2)) as TOTAL,
GROUP BY code1, code2

What I'm trying to do is below

+----+------------+
|  TOTAL  | CODE1 |
+---------+-------+
|  656    |  023  | 
|  9223   |  021  | 
|  205    |  020  |
|  187    |  022  |
+---------+-------+
Mark
  • 49
  • 7

2 Answers2

0

There's union.
But another way is to join 2 sub-queries for the each total.

But then it'll only show the codes that are both in code1 & code2.

SELECT
  q1.code, 
  q1.TOTAL + q2.TOTAL AS TOTAL
FROM 
(
    SELECT code1 AS code, SUM(amount1) AS TOTAL
    FROM your_table
    GROUP BY code1
) q1
INNER JOIN 
(
    SELECT code2 AS code, SUM(amount2) AS TOTAL
    FROM your_table
    GROUP BY code2
) q2
ON q2.code = q1.code
ORDER BY q1.code
LukStorms
  • 28,916
  • 5
  • 31
  • 45
0
SELECT code1, SUM(AMOUNT1) 
FROM 
(
 SELECT code1,  AMOUNT1
 FROM <table>
  UNION ALL
 SELECT code2,  AMOUNT2
 FROM <table>
)
GROUP BY code1
Mber
  • 1
  • 1
  • 1
    Please add some explanation to your answer such that others can learn from it – Nico Haase Jan 07 '22 at 08:41
  • This is incorrect a union will throw away rows with duplicates in code/amount thereby understating the result - UNION ALL would not but this is a copy of an earlier working answer.. – P.Salmon Jan 07 '22 at 08:47