Ok, when you have a GROUP BY clause, you can specifiy the column name, the column alias or an ordinal number representing the column position. That's why `AssignedTo` works -- it is the column alias. `AssignedToAgent`.`AssignedTo` doesn't work because AssignedTo is not a column name in table `Agent`. So, you have three choices on writing your GROUP BY:
GROUP BY 1,2 GROUP BY `Product`.`Product`, `AssignedTo` GROUP BY `Product`.`Product`, `AssignedToAgent`.`AgentName` Use the statement that makes most sense to you. It will save you time if you need to change the statement later. MySQL doesn't act any differently no matter which way you do it. Randy Clamons Systems Programming Astro-auction.com > ------------Original Message------------ > From: "Rhino" <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] > Date: Tue, Nov-23-2004 10:25 AM > Subject: Re: Alias query problem in 4.1.7? > > 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 Windows Mailing List > For list archives: http://lists.mysql.com/win32 > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
