Mysql – calculate the difference between two column aliases in a SELECT statement

MySQLPHP

I'm not sure how to do the last part of my query, which is doing calculations directly with the aliases: (SUM(task_avg – minutes) AS difference. I don't think I can do it as easy as I'd like, but can't find a means to do it. 

$result = mysql_query("SELECT U.user_name, SUM(TA.task_average*M.minutes) AS task_avg, SUM(M.minutes) AS minutes, SUM(task_avg-minutes) AS difference
                            FROM summary S
                            JOIN users U ON U.user_id = S.user_id
                            JOIN tasks TA ON TA.task_id = S.task_id
                            JOIN minutes M ON M.minutes_id = S.minutes_id
                            GROUP BY U.user_name
                            LIMIT 0 , 30");

Best Answer

OK. So I am going to go out on a bit of a limb here. I may have misunderstood the data model in the OP but as I understand it:

1) tasks holds a list of unique tasks

2) TA.task_average = the average time in minutes that a task takes to complete

3) users holds a list of unique users

4) summary represents users attempts at the tasks along with how long each attempt took to complete (through reference to the minutes table)

Gross assumptions out the way here is what I've come up with:

Firstly to get a list of all users, how long they took to complete each task in each attempt and the difference between each user's attempt and the task average:

select U.user_name,
TA.task_id,
TA.task_average,
M.minutes,
M.minutes-TA.task_average as diff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id;

then aggregating that up to get the average difference from the task average for each user (which I think is what the OP was asking for):

select U.user_name,
avg(M.minutes-TA.task_average) as avgDiff
FROM summary S
JOIN users U ON U.user_id = S.user_id
JOIN tasks TA ON TA.task_id = S.task_id
JOIN minutes M ON M.minutes_id = S.minutes_id
group by U.user_name
limit 0,30;

If I have then wrong end of the stick then please ignore this. Thought I'd post it up anyway in case it is useful.

Related Question