GROUP BY/ORDER BY Question

[BobRoyAce]

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

 

[Gerald Stanley]

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

 

[Guest]

Good point about the unneeded GROUP BY...took it out and
all is well! Thanks.