chump

hi,

i know a bit about mySQL as i use it in my PHP pages pretty effectively.

What i can't get my head around though is how to structure a database for average scores? Say if i want to run a 'Poll' that collates an average customer rating for a particular company (there will be multiple companies). Do i need to store the results for each company in a separate table? Then work out the averages on the fly by doing a simple average calculation and displaying the result on the page?

I'm not sure how you could keep a running total - as averages (as far as my maths tells me) are based on all the results added together and then divided by the total instances...

I'm really unsure how to structure this in SQL so that it is efficient....

This might be a bit confusing but I wasn't sure how to explain it...

Thanks

SJ_Skyline

you wouldn't store this information, you would run it on the fly. I am hopeing you have a structure something like:

tblCustomer:
intCustomerID
strCustomerName
.....

tblCompany:
intCompanyID
strCompanyName
.....

tblRating:
intCustomerID
intCompanyID
intRating

In order to get the average rating per company:
SELECT AVG(intRating), DISTINCT(intCompanyID) FROM tblRating
- or -
SELECT AVG(intRating), intCompanyID FROM tblRating GROUP BY intCompanyID

Note: SQL may differ slightly for mySQL


HTH

TopBanana

That's how I'd do it too. You could denormalise it a bit if you never cared about individual ratings...

tblCompany:
intCustomerID
strCompanyName
intCumulativeRating
intRatingN

Then
SELECT intCompanyID, strCompanyName, intCumulativeRating / intRatingN FROM tblCompany

chump

Thanks guys - most helpful