MySQL UNION - Every derived table must have its own alias

Question

I am looking for a solution:

SELECT SUM(`quant`), MONTH(`date`) AS month, `id` from (
(SELECT `date`, `id`, count(`hit`) AS `quant ` FROM `stat_2014_07` WHERE `k_id` = '123') 
UNION ALL 
(SELECT `date`, `id`, count(`hit`) AS `quant ` FROM `stat_2014_08` WHERE `k_id ` = '123') 
) group by id, month

MySQL: Every derived table must have its own alias

Solution 1

You need to give aliases to your queries:

SELECT   SUM(`quant`), MONTH(`date`) AS month, `id` 
FROM     ((SELECT `date`, `id`, count(`hit`) AS `quant` 
           FROM   `stat_2014_07` 
           WHERE  `k_id` = '123') t1
          UNION ALL 
          (SELECT `date`, `id`, count(`hit`) AS `quant` 
           FROM   `stat_2014_08` 
           WHERE  `k_id ` = '123') t2
         ) t_union
GROUP BY id, month

Solution 2

Exactly what the error message says. In your (simplified) query:

SELECT SUM(`quant`), MONTH(`date`) AS month, `id` 
from (
  ... inner select 
) 
group by id, month;

You didn't specify an alias for the derived table. So it should be:

SELECT SUM(`quant`), MONTH(`date`) AS month, `id` 
from (
  ... inner select 
) as t -- this is the change
group by id, month;

Btw: the parentheses around the select parts of a union are totally useless. I suggest removing them for clarity:

SELECT SUM(`quant`), MONTH(`date`) AS month, `id` 
from (
  SELECT `date`, `id`, count(`hit`) AS `quant ` FROM `stat_2014_07` WHERE `k_id` = '123'
  UNION ALL 
  SELECT `date`, `id`, count(`hit`) AS `quant ` FROM `stat_2014_08` WHERE `k_id ` = '123'
) as t -- this is the change
group by id, month;