Re: Mysql 5.1 union with group by for results

2013-11-08 Thread Machiel Richards 

Hi Jesper

I was just discussing this with the development manager now and the 
following was noted.


- The query was written for mysql 4.0 originally and it seems 
that in version 5.0 they had enabled some legacy support stuff ( I am 
not too familiar with this as it is before my mysql time ;-) ).


- I have now explained to them what the problem is and they 
will be working with the developers to rewrite all these queries.


Regards
Machiel.





On 08/11/2013 13:27, Jesper Wisborg Krogh wrote:

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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

2013-11-08 Thread Machiel Richards 

Happiness, that gave me what I was looking for. Thank you Johan.

I have tested the option you gave me but my brackets was in the wrong place.




On 08/11/2013 13:23, Johan De Meersman wrote:

- Original Message -

From: "Machiel Richards" 

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.AccountID,


I suspect your query has never been doing what you think at all, and you need to


   select [fields] from (
 select fields1 blahblah
 UNION
 select fields2 blahblah) unionized
   group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

2013-11-08 Thread Johan De Meersman 
- Original Message -
> From: "Machiel Richards" 
> 
> 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.AccountID,


I suspect your query has never been doing what you think at all, and you need 
to 


  select [fields] from (
select fields1 blahblah
UNION
select fields2 blahblah) unionized
  group by blurb


that is, wrap the entire union in a virtual table and do the group by on that.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql 5.1 union with group by for results

2013-11-08 Thread Jesper Wisborg Krogh 

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

Mysql 5.1 union with group by for results

2013-11-08 Thread Machiel Richards 

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:

(SELECT SUBSTRING(t.Day,1,7) AS Date,
  a.Name AS Account,
  a.Status AS AccountStatus,
  c.Name AS Login,
  t.Service AS Service,
  n.name AS Network,
  tc.Name AS Toc,
  t.SrcAddress AS FromAddress,
  SUM(t.Count) AS COUNT
   FROM statstx_archive t
   LEFT JOIN account a ON t.AccountID=a.ID
   LEFT JOIN client2 c ON t.ClientID=c.ID
   LEFT JOIN tocname tc ON t.TOC=tc.toc
   LEFT JOIN network n ON t.NetworkID=n.ID
   WHERE t.toc=1
 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')
UNION
  (SELECT SUBSTRING(t.Day,1,7) AS Date,
  a.Name AS Account,
  a.Status AS AccountStatus,
  c.Name AS Login,
  t.Service AS Service,
  n.name AS Network,
  tc.Name AS Toc,
  t.SrcAddress AS FromAddress,
  SUM(t.COUNT) AS COUNT
   FROM statstx t
   LEFT JOIN account a ON t.AccountID=a.ID
   LEFT JOIN client2 c ON t.ClientID=c.ID
   LEFT JOIN tocname tc ON t.TOC=tc.toc
   LEFT JOIN network n ON t.NetworkID=n.ID
   WHERE t.toc=1
 AND SUBSTRING(t.Day,1,7) >= '2013-08'
 AND SUBSTRING(t.Day,1,7) <= '2013-11')
GROUP BY t.AccountID,
 t.ClientID,
 t.Service,
 t.NetworkID,
 t.Toc,
 t.SrcAddress,
 SUBSTRING(t.Day,1,7)



When running the queries individually it works fine, and 
when removing the group by it also runs, however the moment we put the 
group by section back then it falls over.


Any help would by appreciated.

Regards
Machiel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql