mayo wrote: > > SELECT MONTH(saleDate) AS theMonth, sum(saleTotal) as dailyTotal > FROM sales > GROUP by theMonth
I know you already know how to solve your problem, but please bear with me :) The reason that your query isn't supposed to work (it will work in some implementations) is that DBMS's are supposed to derive the result from queries in a specific way. First, they should take the FROM clause, expand the column list from all the tables listed and remove any columns wrong JOIN with the NATURAL or USING modifiers. Then they should prune all the rows that do not match the WHERE clause. After that they should process the GROUP BY, then the HAVING and finally the SELECT. So there you have it: at the point where the DBMS has to do the actual grouping, the SELECT clause isn't processed yet so the alias that is assigned in the SELECT is not known yet. And that is actually a good thing because it removes any ambiguousness from the case where table X has the fields Y and Z and you issue the query "SELECT y AS z FROM x WHERE z = 1". Since the alias z is not known yet the z in the WHERE clause has to refer to the column z. This order of processing is one of the reasons why I always start writing my queries by writing the FROM, then I write the WHERE, and then the whole rest. Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:210451 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
