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