Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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");
share|improve this question

migrated from stackoverflow.com Oct 19 '12 at 9:13

This question came from our site for professional and enthusiast programmers.

1  
If you don't need to retrieve task_avg and minutes, I believe you can just do the operations all in one, without the two aliases, to get the difference...SUM(SUM(TA.task_average*M.minutes) - SUM(M.minutes)) AS difference –  andbeyond Oct 19 '12 at 0:53
    
Even if you want task_avg and minutes; you can still use the method as mentioned by @andbeyond –  hjpotter92 Oct 19 '12 at 0:56
    
Tried that and variations of it and got: #1111 - Invalid use of group function –  Mike Oct 19 '12 at 0:56
1  
Otherwise, if you need to retrieve everything, wrap your current query (without the difference calculation) in another SELECT statement that selects task_avg and minutes, then do your difference calculation in the wrapped query –  andbeyond Oct 19 '12 at 0:56
    
I need some help...not sure the best way to wrap this. If there is a specific function you can point me to, I can look it up. Just not sure how to do it. –  Mike Oct 19 '12 at 1:18

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
100% on the money - exactly what I was looking for!! Thank you sir!! –  Mike Oct 19 '12 at 12:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.