looks like you simply forgot to use a GROUP BY clause
SELECT user_id
, AVG( value_close - value_open) / value_open )
AS average_gain
FROM item_values
WHERE round_id = X
GROUP
BY user_id
ORDER
BY average_gain ASC
note there wasn’t a user_id in your item_values table, but i assume this was just an oversight
Basically, the whole thing is driven by the item_picks table.
Each user has 5 items in item_picks for round X. Based on their picks, calculate their average gain based on value_open and value_close in item_values table and rank all users based on average gain.
SELECT p.user_id
, AVG( v.value_close - v.value_open) / v.value_open )
AS average_gain
FROM item_picks AS p
INNER
JOIN item_values AS v
ON v.item_id = p.item_id
WHERE p.round_id = X
GROUP
BY p.user_id
ORDER
BY average_gain ASC
btw, you have the “tournament” part right. It is an online pool, but it is not for NCAA. I cant really advertise it yet, but I’ll make sure to let you know what it is all about.
I seem to be getting weird numbers for the average percentage gain. I’d appreciate your help.
The “pool” in question is a stock pool and I’m getting all the actual numbers from the exchanges.
I calculated the average percentage gain manually, and with the SQL query above, they turn out quite differently. I have also tried a bunch of other queries, both from scratch and variations of your query above, but they all seem to fail.
What am I trying to do?
Each user has 10 companies picked for pool id X in pool_picks table.
Each company has an open and close price in companies_in_pool table
I’m trying to get average gain for ALL users for pool X.
The query below works…for 1 user only though. I could programatically make it run for each user, but scalability becomes a big issue for a bigger list of users
SELECT
AVG( ((p.`pool_end_price`-p.`pool_start_price`)/p.`pool_start_price`)*100 )
FROM `companies_pool` p
LEFT JOIN
`companies` c
ON c.company_id = p.`company_id`
WHERE
p.company_id IN (69,105,20,94,30,132,44,117,85,79) <-- these are the exact user picks
AND p.pool_id=2;
yeah, maybe i can help, but first i have to understand what you’re doing
what constraints if any do you have in place to ensure that a row in the pool_picks table for a given user actually corresponds to a particular company in a particular pool? in other words, is the combination of pool_id and company_id in pool_picks always guaranteed to be found in the companies_in_pool table?
next, please explain this: “average gain for ALL users for pool X”
as far as i can tell, the answer would be given by your query without the WHERE condition on companies – i.e. with only the pool being selected, calculate the average for all the companies in the pool, right? and if not all companies in the pool, why not?
sorry for the loose explanations. It made sense in my head, but I’ve been looking at this all day, so that’s prolly why.
what constraints if any do you have in place to ensure that a row in the pool_picks table for a given user actually corresponds to a particular company in a particular pool? in other words, is the combination of pool_id and company_id in pool_picks always guaranteed to be found in the companies_in_pool table?
Yes, ALWAYS. I’ve made sure that ALWAYS happens
next, please explain this: “average gain for ALL users for pool X”
I tihnk I can answer that graphically.
Consider the following, assuming the current pool being run has pool_id XXX.
YYY is for previous pools (used only for illustration purposes. not in consideration for current pool)
User 1 picks company A and company B
User 2 picks company Y and company Z
`pool_id` `user_id` `company_id`
XXX 1 A
XXX 1 B
XXX 2 Y
XXX 2 Z
YYY 1 X // old picks
YYY 1 Y // old picks
YYY 2 X // old picks
YYY 2 z // old picks
companies_in_pool table is
pool_id company_id pool_start_price pool_end_price
XXX A 10 20
XXX B 5 10
XXX Y 3 5
XXX Z 25 20
YYY A 100 200 // prices for other pools
YYY B 50 100 // prices for other pools
YYY Y 30 50 // prices for other pools
YYY Z 250 200 // prices for other pools
User 1 average % gain is
- line 1 company A -- -- line 2 company B-- /2 for average
( ((20 - 10)/10 )*100 + ( (10 - 5)/5) * 100 ) / 2
Same is done for User 2.
I’m trying to get both calculations of averages for user 1 and user 2 in one query.
user_id average_gain (%)
1 100
2 23
Does that answers your questions? Please let me know. I’ll elaborate further if needed
SELECT pp.user_id
, AVG( 100.0 * ( cp.pool_end_price -
cp.pool_start_price )
/ cp.pool_start_price ) AS avg_gain
FROM pool_picks AS pp
INNER
JOIN companies_in_pool AS cp
ON cp.company_id = pp.company_id
WHERE pp.pool_id = 2
GROUP
BY pp.user_id
