Adding values of column of 2 tables by using another common column as key in sql in table transformer macro in Atlassian confluence

Question

I am new to SQL in Atlassian and have a query as there is some difference between my daily used SQL and the SQL used in table transformer macro in Atlassian confluence

I want to create an SQL query that can be used in table transformer macro in Atlassian confluence. It should sum up the column values of two tables having the same header name and full-join them by using another common column as a key.Let's say I have 2 tables given below

Table 1

Key	num
katie	23
Jack	41
June	43

Table 2

Key	num
paty	20
Jack	21
June	4

And I want the obtain the below table through an "Atlassian-valid" SQL

Key	num
Katie	23
paty	20
Jack	62
June	47

Can u please help me to get this?

follow the course: https://marketplace.atlassian.com/apps/225/sql-for-confluence-pro-edition?tab=overview&hosting=cloud ? — Luuk, Feb 25 '22 at 18:41
MySQL doesn't support `FULL JOIN`. See https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql?noredirect=1&lq=1 — Barmar, Feb 25 '22 at 18:52

Luuk · Accepted Answer · 2022-02-26T11:21:33.393

1

Your can try (in SQL, I do not know Attlassian products):

SELECT
     `key`,
     SUM(Num) as Num
FROM (
     SELECT `key`, Num
     FROM Table1
     UNION ALL
     SELECT `key`, Num
     FROM Table2
     ) x
GROUP BY `key`
ORDER BY `key`

DBFIDDLE

edited Feb 26 '22 at 11:21

answered Feb 25 '22 at 19:47

Luuk

12,245
5
22
33

Hi @Luuk, I have a doubt, if there is a key present in one table and not present in another table, from the above example, let's take 'katie' whih=ch is present in T1 and not present in T2 since we are adding them both, the outcome would be '23+null' which give null. So if we use sum(num) here, could we get 23 instead of null. If not, can u please write an SQL query which does the same? Thanks for the answer though – sai velagala Feb 25 '22 at 20:05
Sry I have tried your code, but it is not working as the answer solution is like printing only one row with summing up all num present in it. But we use 'GROUP BY ' with I am getting the correct answer I need. But thanks for your answer. I got Idea due to it – sai velagala Feb 26 '22 at 06:00
oops, i did forget to add the `GROUP BY`, see the edit and the DBFIDDLE – Luuk Feb 26 '22 at 11:19

Adding values of column of 2 tables by using another common column as key in sql in table transformer macro in Atlassian confluence

1 Answers1