cte FULL OUTER JOIN MYSQL

I have this table(called trial):

a, id1, year1, year2, val1, val2
1, A01, 2015, 2025, 17.20, 18.30
2, A02, 2014, 2024, 13.20,  7.80
3, A04, 2013, 2024, 10.00, 10.00
4, A03, 2024, 2014, 12.20,  8.80
5, A05, 2024, 2014,  0.00,  6.00

‘year1’ refers to values in Column ‘val1’, ‘year2’ to values in Column ‘val2’.

I want to get totals of all values grouped by year.

So I would like to see a result like:

Year    Value per year
2013    10.0
2014    28.0
2015    17.2
2024    30.0
2025    18.3

I set up a common table expression(cte) using a self join to do this as follows:

SELECT  t1.year1, t2.year2, t1.val1, t2.val2,  t1.val1+t2.val2 AS val_tot FROM trial t1 FULL OUTER JOIN trial t2 ON t1.year1 = t2.year2  GROUP BY t1.year1;

(The first four output columns are added to get clarity concerning what is going on)

The output is:

year1   year2   val1    val2    val_tot
2013    NULL    10.0    NULL    NULL
NULL    2025    NULL    18.3    NULL
2015    NULL    17.2    NULL    NULL
2014    2014    13.2     6.0    19.2
2024    2024     0.0     7.8     7.8

The output is obviously just not correct.

I have tried modifications to the SELECT statement above such as changing the JOIN type, changing the GROUP BY clause or leaving it out, and adding a WHERE clause, without success. I have Googled and read websites with no success as I can’t get a similar example although there must be many identical questions.

Many thanks for any help.

Answer

If I understand, you want to unpivot and then aggregate. One method is:

select year, sum(val)
from ((select year1 as year, val1 as val from trial t) union all
      (select year2, val2 from trial t)
     ) t
group by year
order by year;

Advertisement

Answer