Adding with sql

The_Goose

Hey need help, try to get the avrage of rating entered into a table!

I have autonumber to count, but need help adding the values in the rating column and the dividing that by the other column

please hurry

d4r3n

SELECT (COUNT(autonumber) / SUM(rating)) AS rating FROM table WHERE ... ?

any examples of what you've attempted?

The_Goose

Looking at that you do't want to know
SELECT (COUNT(ID) / SUM(Rating)) AS Rating FROM pole WHERE ID = MAX ?
would that work
need to store that value in the table aswell????

d4r3n

SELECT (MAX(ID) / SUM(Rating)) AS Rating FROM pole

divide the highest autonumber by all the ratings added together?

The_Goose

yeah like every time somone add s a separate rating to the table, there s 20 entry ', 5,4,3,1,2,5,4,3,5,1,2,5,1,3,5,5,3, etc need to add them together then divide by IDmax then store that value in the table

d4r3n

INSERT INTO ratings (avg_rating) SELECT (SUM(rating) / MAX(ID)) AS rating FROM pole

really basic stuff here, might want to get an SQL book or something/

bonkey

Originally posted by d4r3n
INSERT INTO ratings (avg_rating) SELECT (SUM(rating) / MAX(ID)) AS rating FROM pole

This will work as long as you have no null values, and no row has ever been deleted from the table and the ID starts at 1, and increases monotonously (i.e. no gaps) by 1 across all records.

I wouldn't trust any set of changeable data to obey those rules.

So, for a start I'd use :

SELECT (SUM(rating) / COUNT(rating)) as rating_average FROM pole

Then I'd consider if NULL rating values were possible, and if so, what should be done with them (i.e. do they get ignored, or treated as 0 values) and see if the query needs further modification (which it probably would).

really basic stuff here, might want to get an SQL book or something

I'd agree with that, but as I just pointed out...."really basic" can still have a huge amount of pitfalls just waiting to catch you up, so its not enough to just

jc