Geek Answers Handbook

MySQL UNION - Each view must have its own alias

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: each view must have its own alias

+4
source share
2 answers

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
+7
source

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

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

You did not specify an alias for the view. Therefore, 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: parentheses around select parts of a pool are completely 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;
+10
source

More articles:


All Articles