Search code examples
mysqlselectunion

MySQL union select result to column

I use below query to get a total value per year and a total value per month at once, the result is two rows but I would like to have the result as columns. Can anybody help out? Thanks!

SELECT SUM(a.smd24_18v_act)
FROM smd24_log_monthly as a
WHERE  a.smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE
AND NOT YEAR(a.smd24_date) = YEAR(CURRENT_DATE)
UNION
SELECT SUM(b.smd24_18v_act)
FROM smd24_log_monthly as b
WHERE MONTH(b.smd24_date) = 1
AND b.smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE
AND NOT YEAR(b.smd24_date) = YEAR(CURRENT_DATE);
Solution

  • Use a single pass query with conditional aggregation:

    SELECT
    SUM(smd24_18v_act) AS val1,
    SUM(CASE WHEN MONTH(smd24_date) = 1 THEN smd24_18v_act ELSE 0 END) AS val2
FROM smd24_log_monthly
WHERE smd24_date BETWEEN '2017-01-01' AND CURRENT_DATE AND
      NOT YEAR(smd24_date) = YEAR(CURRENT_DATE);