MariaDB subqueries to same table and column resulting in several columns

mariadb

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB

Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.

SELECT RD, OT1, OT2, OT3 FROM
(SELECT rdate from  OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:

RD                   OT1    OT2    OT3
2006-07-01 00:00:00  1.2345 2.1234 1.543
...                  ...    ...    ...
2006-07-31 23:59:00  3.456  3.234  2.234

And, no I dont want to use UNION because then they will still follow one after the other…

Any thoughts?!

Best Answer

You are pivoting too soon. Do the union, then pivot the results.

Related Question