group by - error message

[catchingup]

In the final step of a stored procedure I am writing, I get this error when trying to group by 'ID'

How do I fix this error? Do I need to rename this field?

Here is the query:

SELECT #tempWeeklyInfo.ID,
#tempWeeklyInfo.Num, #tempWeeklyInfo.StatsID,
CASE WHEN #tempWeeklyInfo.TotalHours <> 0
THEN ((#tempWeeklyInfo.Mtotal / #tempWeeklyInfo.TotalHours) * 100) else 0 END as MTotalPerc,

CASE WHEN #tempWeeklyInfo.TotalHours <> 0
THEN ((#tempWeeklyInfo.FemaleHours / #tempWeeklyInfo.TotalHours) * 100) else 0 END as FemalePerc,

CASE WHEN #tempWeeklyInfo.TotalHours <> 0
THEN ((#tempWeeklyInfo.CityHours / #tempWeeklyInfo.TotalHours) * 100) else 0 END as CityPerc
INTO #tempFinalSet
FROM #tempWeeklyInfo
GROUP BY #tempWeeklyInfo.ID

Column '#tempWeeklyInfo.Num' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[hneal98]

You have to add #tempWeeklyInfo.Num in the group by clause. Just add a comma after the #tempweeklyinfo.id and add #tempWeeklyInfo.Num after it.

 

[ptheriault]

You will get the same error for #tempWeeklyInfo.StatsID, if you don't add it to your group by clause as well. Your group by must have all column that are not in the aggrigates.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[catchingup]

Thank you - this work but I now am getting a strange message:

"The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

The field i want to group by is a varchar. Do I need to cast it as something else?

 

[nice95gle]

Adding to Paul's statement.

Any column that is a straight select from your table needs to be in your group by. Calculated columns need not be in your group by.

#tempWeeklyInfo.ID,#tempWeeklyInfo.Num, #tempWeeklyInfo.StatsID all needs to be in your group by statement; columns like MTotalPerc is calculated so it will not be needed. But when you add grouping to more than one column the expected results might vary.


Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Group by or Order by a text, ntext or image data type is not allowed.

Well Done is better than well said
- Ben Franklin