I have the following tables with the following fields:
User: uid (and various other fields that don't matter)
Shows: sid, hits, paidfor, uid
I need to do the following:
1) Get the average number of hits for shows where a user has more than 10 shows (doesn't depend on if they are paid for or not). I have the following query, and it seems to work appropriately for that:
SELECT AVG(hits) as totalhits FROM Shows, User WHERE Shows.uid= User.uid GROUP BY Shows.uid HAVING numshows > 10
I run the results through a loop to total up the totalhits, and divide by the number of shows to get the overall average. Does this sound right, math-wise?
2) I need to get the average number of hits for paid for shows where a user has more than 10 shows. My problem is this -- the number of shows is based on the TOTAL number of shows a person has, regardless of whether or not they are paid for. But the average hits needs to be based on their paid for shows. So:
SELECT AVG(hits) as totalhits FROM Shows, User WHERE Shows.uid= User.uid AND paidfor = '1' GROUP BY Shows.uid HAVING numshows > 10
That won't work because that will remove all shows from consideration in the user show total that are not paid for -- and I need that total to include both paid for and non-paid for shows.
Does anyone see a way around this? I have mySQL 4.0.20 and cannot upgrade to 4.1 to do subqueries (which seemed like the easiest way to do this). Any suggestions are greatly appreciated!!
