how to use union query on the same table and different group by in mysql?

Question

I have sql query like this:

select a.x, a.y, sum(a.foo) as foo_sum
from a
group by a.x

and after running this query, I can access to foo_sum.

I want to do something like this:

select a.x, a.y, sum(a.foo) as foo_sum
from a
group by a.x
union
select a.x, a.y, sum(a.bar) as bar_sum
from a
group by a.y

but after running this one, my program dosent know foo_sum, anybody knows why?

thank you.

Edit: I want to have 4 column x, y, foo_sum and bar_sum, is this possible?

Answer 1

I don't think this is possible, because your number of unique x values might not be the same as the number of unique y values.

For example, suppose a was like this:

+------+------+------+------+
| x    | y    | foo  | bar  |
+------+------+------+------+
|    1 |    1 |    3 |    4 |
|    1 |    2 |    3 |    6 |
|    1 |    1 |    3 |    6 |
|    1 |    2 |    8 |    3 |
|    2 |    1 |    7 |   32 |
|    2 |    2 |    7 |   34 |
|    2 |    3 |    8 |    3 |
+------+------+------+------+

The first query (foo_sum GROUP BY x) would give:

+------+------+---------+
| x    | y    | foo_sum |
+------+------+---------+
|    1 |    1 |      17 |
|    2 |    1 |      22 |
+------+------+---------+

The second (bar_sum GROUP BY y) gives:

+------+------+---------+
| x    | y    | bar_sum |
+------+------+---------+
|    1 |    1 |      42 |
|    1 |    2 |      43 |
|    2 |    3 |       3 |
+------+------+---------+

How can you mash the bar_sum column on the end of the foo_sum table? There is no corresponding foo_sum value for (x,y)=(2,3), but there is a corresponding bar_sum.

The best you could achieve would be something like this:

+------+------+---------+-------+
| x    | y    | bar_sum |foo_sum|
+------+------+---------+-------+
|    1 |    1 |      42 |    17 |
|    1 |    2 |      43 |  NULL |
|    2 |    3 |       3 |  NULL |
|    2 |    1 |    NULL |    22 |
+------+------+---------+-------+

To achieve this the only way I can think of is using a FULL OUTER JOIN. Note that doing SELECT x, y, SUM(foo), SUM(bar) FROM a GROUP BY x,y won't give the same results as it groups differently.

SELECT t1.x,t1.y,foo_sum,bar_sum
FROM
(SELECT x, y, SUM(foo) as foo_sum
FROM A
GROUP BY x) t1
FULL OUTER JOIN
(SELECT x, y, SUM(bar) as bar_sum
FROM A
GROUP BY y) t2
ON t1.x=t2.x AND t1.y=t2.y

This makes sure all the x,y combinations from each table is inserted, even if there are no corresponding x,y in the other table.

However MySQL has no FULL OUTER JOIN, and you have to simulate a A FULL OUTER JOIN B by UNIONing a LEFT and RIGHT join together:

SELECT ...
 FROM A LEFT JOIN B ON ...
UNION
SELECT ...
 FROM A RIGHT JOIN B ON ...

In your case this translates to the very ugly:

SELECT t1.x,t1.y,foo_sum,bar_sum
FROM
(SELECT x, y, SUM(foo) as foo_sum
FROM A
GROUP BY x) t1
LEFT JOIN                        -- need FULL OUTER JOIN
(SELECT x, y, SUM(bar) as bar_sum
FROM A
GROUP BY y) t2
ON t1.x=t2.x AND t1.y=t2.y

UNION

SELECT t2.x,t2.y,foo_sum,bar_sum
FROM
(SELECT x, y, SUM(foo) as foo_sum
FROM A
GROUP BY x) t1
RIGHT JOIN                        -- need FULL OUTER JOIN
(SELECT x, y, SUM(bar) as bar_sum
FROM A
GROUP BY y) t2
ON t1.x=t2.x AND t1.y=t2.y

This is highly inefficient ! I suggest you instead do the grouping on the PHP side (or whatever other language you are using with MySQL) than the SQL side.

Who knows, there may be a more efficient way to do this since all you want is SUMs - there could be a clever way to group and sum to achieve your effect.

Answer 2

Is this same as:

select a.x, a.y, sum(a.foo) as foo_sum, sum(a.bar) as bar_sum 
from a 
group by a.x ,a.y