MySql get sum of a subquery, the subquery has a union

Question

I'm new to mysql but not new to sql. Below is my code and error message

Error: 42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select content_type, count(*) as Red, 0 as Green from touchpoints ' at line 3

SELECT content_type, SUM(Red), SUM(Green)
FROM (
         select content_type, count(*) as Red, 0 as Green
         from touchpoints
         where evaluatee_user_id = 6
           and color_code = 'R'
         group by content_type
         UNION
         select content_type, 0 as Red, count(*) as Green
         from touchpoints
         where evaluatee_user_id = 6
           and color_code = 'G'
         group by content_type
    )
GROUP BY content_type

The subquery works and the output is:

M   1   0
SCI 5   0
SFA 3   0
SS  1   0
M   0   3

You need a table alias after the `)`. I consider this a typo and vote to close. — Gordon Linoff, Feb 10 '20 at 19:09
For Pivoting in MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:29

score 1 · Answer 1 · answered Feb 10 '20 at 19:10

1

You can simplify this query by using conditional aggregation:

 select content_type, sum(color_code = 'R') as Red, sum(color_code = 'G') as Green
 from touchpoints
 where evaluatee_user_id = 6
 group by content_type;

No subquery is needed at all.

answered Feb 10 '20 at 19:10

Gordon Linoff

1,242,037
58
646
786

This was a perfect alternative to my mes – Jim Kiely Feb 10 '20 at 19:16

MySql get sum of a subquery, the subquery has a union

1 Answers1