Try changing your GROUP BY to use the column name of the second column in the SELECT, not the alias of the second column name, i.e.
GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName` That should work on any version of MySQL. I don't think you're allowed to use aliases in a GROUP BY, only actual column names. Then again, I am mostly a DB2 person and the MySQL dialect of SQL may allow aliases in a GROUP BY for all I know. I'm suprised that the alias worked on 3.2.3: are you sure you have reproduced the exact query that works on 3.2.3? I'm afraid I don't have either a 3.2.3 or 4.1.7 system so I can't try any of this for myself to see. By the way, did you realize that your query is substantially longer than it needs to be? You really only need to qualify column names with table names if you are doing a join of two or more tables and even then, you only need to qualify column names that occur in two or more of the tables read by the query. That would also eliminate the need for you to write aliases for some of your table names at all, further shortening the query. In your query, it appears that only the 'ProductKey' column occurs in more than one of the tables so your query could be as short as this: SELECT `Product`, `AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` ON `AssignedToKey` = `AgentKey` INNER JOIN `DateDim` ON `DateOpenKey` = `DateDimKey` WHERE `Year` = '2003' GROUP BY `Product`, `AssignedTo`; Then again, perhaps it is your shop standard to fully qualify all column names in queries; if so, you should follow your shop standard ;-) More likely, you are probably using some sort of query generating tool in which case you probably don't have a choice in the matter. Rhino ----- Original Message ----- From: "Geoffrey R. Thompson" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 23, 2004 12:11 AM Subject: Alias query problem in 4.1.7? I have an interesting problem that I cannot find any clues to in the MySQL documentation. The following query works in 3.2.3, but does not work in 4.1.7: SELECT `Product`.`Product`, `AssignedToAgent`.`AgentName` AS `AssignedTo`, sum(`Inquiries`) AS `Inquiries` FROM `Inquiry` INNER JOIN `Product` ON `Inquiry`.`ProductKey` = `Product`.`ProductKey` INNER JOIN `Agent` AS `AssignedToAgent` ON `Inquiry`.`AssignedToKey` = `AssignedToAgent`.`AgentKey` INNER JOIN `DateDim` AS `DateOpen` ON `Inquiry`.`DateOpenKey` = `DateOpen`.`DateDimKey` WHERE `DateOpen`.`Year` = '2003' GROUP BY `Product`.`Product`, `AssignedToAgent`.`AssignedTo`; It appears that if I take the table alias "AssignedToAgent" out of the GROUP BY clause (leaving just the column alias "AssignedTo"), the query will then work in 4.1.7 - even though the table alias does not present a problem in 3.2.3. Any ideas why? Any help would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
