List:General Discussion« Previous MessageNext Message »
From:Machiel Richards Date:November 8 2013 9:04am
Subject:Mysql 5.1 union with group by for results
View as plain text  
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
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