Error message when trying to do a sum in SQL using designer

[thechazm]

I am having a problem getting the sum in this query. It keeps giving me an error message (You tried to execute a query that does not include the specified expression "ID" as part of an aggregate function. (Error 3122)).

Can someone please shed some light on this for me because I have done plenty of calculations without this error before... Below is my SQL. Thank you!

SELECT [ZoneManagers Request].ID, 
[ZoneManagers Request Full Week].Week1R, 
[ZoneManagers Request Half Week].Week1R, 
Sum([ZoneManagers Request Full Week].[Week1R]+([ZoneManagers Request Half Week].[Week1R]/2)) AS TotalWeek1
FROM ([ZoneManagers Request] 
LEFT JOIN [ZoneManagers Request Full Week] ON [ZoneManagers Request].ID = [ZoneManagers Request Full Week].[Request ID]) 
LEFT JOIN [ZoneManagers Request Half Week] ON [ZoneManagers Request].ID = [ZoneManagers Request Half Week].[Request ID];

 

[PNGBill]

You are not doing any favours for yourself with spaces in your names.
ID isn't on my list of Reserved Words but I would not be surpirsed that it was one.

I don't think you can Sum a Calculated Field.

Try two sqls or even a Sub Query

 

[Pat Hartman]

Either remove the Sum() function or Add a Group By clause. Based on the fields you have selected, I'm going to guess that removing the Sum() is the correct solution. You can add two columns without using Sum(). The Sum() function is used to aggregate data over some number of records. If you use Sum() you'll need to get rid of the first three fields you have in the select clause or you'll need to include them in a Group By. Including them in a Group By will prevent data from actually aggregating which is why my suggestion is to just get rid of the Sum(). It is not doing what you think it is doing.

Ditto on the names.

 

[thechazm]

So I guess I really don't understand why I couldn't use the sum function in this example but wouldn't group by group all the records by those values?

The reason why I am asking is I need to do this same calulation in the same SQL query 12 times and then other calculations as well. The reason for it being calculated twelve times is that its for each week.

Eg.

Week1R + Week1R / 2
Week2R + Week2R / 2
Week3R + Week3R / 2

etc..

There are a ton other calculations that I will need to do as well outside of this specific one.

So what is the best approach to this problem if you don't mind me asking? I am ok / half way decent with SQL but some of this stuff eludes me :/

Thanks again for all your help and thanks for the backup on the spaces in the names.

 

[thechazm]

Please disregard my last post. I was not thinking clearly and I appreciate the help. I was thinking of a crosstab query and not a totals query... It works now and thank you for your time