If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
GROUP BY/ORDER BY Question
I inherited a database which had the following query:
SELECT MonthData.FiscalMonthNumber, (100* (SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number WHERE TableA.FiscalYear=2004 GROUP BY MonthData.FiscalMonthNumber, (100* (SumOfKits/SumOfHours)) So, then, I changed it as follows so as to display month names instead of numbers: SELECT MonthNames.MonthName, MonthData.FiscalMonthNumber, (100*(SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthNames INNER JOIN (MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number) ON MonthNames.Month Order = MonthData.FiscalMonthNumber WHERE TableA.FiscalYear=2004 GROUP BY MonthNames.MonthName, MonthData.FiscalMonthNumber, (100*(SumOfKits/SumOfHours)) ORDER BY MonthData.FiscalMonthNumber This worked fine except that I actually don't want the MonthData.FiscalMonthNumber field to come back. When I changed the query once again as follows, I get the error message "You tried to execute a query that does not include the specified expression 'MonthData.FiscalMonthNumber' as part of an aggregate function". What am I missing? SELECT MonthNames.MonthName, (100*(SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthNames INNER JOIN (MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number) ON MonthNames.Month Order = MonthData.FiscalMonthNumber WHERE TableA.FiscalYear=2004 GROUP BY MonthNames.MonthName, (100* (SumOfKits/SumOfHours)) ORDER BY MonthData.FiscalMonthNumber |
#2
|
|||
|
|||
GROUP BY/ORDER BY Question
You cannot have a column in an ORDER BY clause if it is not
in the GROUP BY clause. I cannot see why there is the need for the GROUP BY clause anyway so I would suggest removing it completely and seeing if it gives you the result that you are after. Hope This Helps Gerald Stanley MCSD -----Original Message----- I inherited a database which had the following query: SELECT MonthData.FiscalMonthNumber, (100* (SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number WHERE TableA.FiscalYear=2004 GROUP BY MonthData.FiscalMonthNumber, (100* (SumOfKits/SumOfHours)) So, then, I changed it as follows so as to display month names instead of numbers: SELECT MonthNames.MonthName, MonthData.FiscalMonthNumber, (100*(SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthNames INNER JOIN (MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number) ON MonthNames.Month Order = MonthData.FiscalMonthNumber WHERE TableA.FiscalYear=2004 GROUP BY MonthNames.MonthName, MonthData.FiscalMonthNumber, (100*(SumOfKits/SumOfHours)) ORDER BY MonthData.FiscalMonthNumber This worked fine except that I actually don't want the MonthData.FiscalMonthNumber field to come back. When I changed the query once again as follows, I get the error message "You tried to execute a query that does not include the specified expression 'MonthData.FiscalMonthNumber' as part of an aggregate function". What am I missing? SELECT MonthNames.MonthName, (100*(SumOfKits/SumOfHours)) AS KitsPerHour FROM MonthNames INNER JOIN (MonthData INNER JOIN TableA ON MonthData.System Month Number = TableA.System Month Number) ON MonthNames.Month Order = MonthData.FiscalMonthNumber WHERE TableA.FiscalYear=2004 GROUP BY MonthNames.MonthName, (100* (SumOfKits/SumOfHours)) ORDER BY MonthData.FiscalMonthNumber . |
#3
|
|||
|
|||
GROUP BY/ORDER BY Question
Good point about the unneeded GROUP BY...took it out and
all is well! Thanks. |
Thread Tools | |
Display Modes | |
|
|