List:General Discussion« Previous MessageNext Message »
From:Jesper Wisborg Krogh Date:November 8 2013 11:27am
Subject:Re: Mysql 5.1 union with group by for results
View as plain text  
Hi Machiel,

On 8/11/2013 20:04, Machiel Richards wrote:
> Good day all
>
>        I am hoping someone can assist me in the following.
>
>         One of our servers were running mysql 5.0 still and as part of 
> a phased upgrade route we have upgraded to version 5.1.
>
>         However since the upgrade, the query below gives us an error 
> stating that the syntax is incorrect and I simply cant seem to find 
> out what is actually wrong as all tests and changes have been giving 
> us the same.
>
>         I have tried many suggestions from the net but to no avail.
>
>         The query is as follows:

Using a shorter but equivalent query, you have:

    (SELECT t.id, t.name, SUM(val) FROM t1 t)
    UNION
    (SELECT t.id, t.name, SUM(val) FROM t2 t)
    GROUP BY t.id, t.name;

That does not work in 5.0 either (at least in 5.0.96):

    ERROR 1064 (42000): You have an error in your SQL syntax; check the
    manual that corresponds to your MySQL server version for the right
    syntax to use near 'GROUP BY t.id, t.name' at line 4


The issue is that you are trying grouping the entire UNION result, but 
at that point there is no SELECT any longer - there is just the result 
set. You are also referencing tables that exists inside each of the 
SELECT statements, but at the time the GROUP BY is reached, there are no 
tables. Note that as written the two SELECT parts will also give 
non-deterministic results as you have an aggregate function but no GROUP 
BY, so the values of id and val1 will be "random".

What you probably want instead is either:

    (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
    UNION
    (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);

or

    SELECT a.id, a.name, SUM(val)
       FROM (
             (SELECT t.id, t.name, t.val FROM t1 t)
            UNION
             (SELECT t.id, t.name, t.val FROM t2 t)
            ) a
      GROUP BY a.id, a.name;


On a side note:

>
>      AND SUBSTRING(t.Day,1,7) >= '2013-08'
>      AND SUBSTRING(t.Day,1,7) <= '2013-11')

Assuming t.Day is a date, datetime, or timestamp column, you can rewrite 
that WHERE clause to something like (depending on the exact data type):

    t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'

or

    t.Day >= '2013-08-01 00:00:00' AND t.Day < '2013-12-01 00:00:00'


That way you will be able to use an index for that condition.

Best regards,
Jesper Krogh
MySQL Support

Thread
Mysql 5.1 union with group by for resultsMachiel Richards8 Nov
  • Re: Mysql 5.1 union with group by for resultsJesper Wisborg Krogh8 Nov
    • Re: Mysql 5.1 union with group by for resultsMachiel Richards8 Nov
Re: Mysql 5.1 union with group by for resultsJohan De Meersman8 Nov
  • Re: Mysql 5.1 union with group by for resultsMachiel Richards8 Nov