MYSQL - sum doesnt sum as intended

Question

It is 04.17 in the morning and i cant figure what is wrong! Help please!

I have table with such columns in table user_logs:

id | user_id | action | reference | time
----------------------------------------
1  |   1     | login  |     0     | 1333800404
2  |   1     | logout |     1     | 1333800424
3  |   1     | login  |     0     | 1333800434
4  |   1     | logout |     3     | 1333800444

And query:

SELECT reference r,
  sum(time-(SELECT time FROM users_logs WHERE id = r)) time_of_logon
FROM users_logs
WHERE user_id = 1 AND action = 'logout'

Unfortunately sum() function returns unexpected value.

If I remove sum() I get such a query:

SELECT reference r,
  (time-(SELECT time FROM users_logs WHERE id = r)) time_of_logon
FROM users_logs
WHERE user_id = 1 AND action = 'logout'

and result:

r | time_of_logon
-----------
1 | 20
3 | 10

As expected - i printed all rows with action 'logout' of user_id = 1 decrementing time of logoff from time i got from subquery (time of login connected with logout). Now i have time user was logged in. So far so good. Now, when I add sum (like in first query) I would expect sum of time_of_logon (should be 30). If I put avg function instead of sum i would expect 15 ((10+20)/2).

Sum version: I get 60, avg version - i get 30.

Type of 'time' field is integer.

My guessing: I guess that mysql somehow returns not 2 rows as it shows but it's working on 4 rows, or doubles some calculations beneath the main query. Because both - avg and sum result is twice as big as it should be.

Maybe it's the time issue that my brain is not working anymore but i cant figure out what is wrong. Please help.

That inner select looks awful. What are you trying to do and what is your expected result from the given table? — Mosty Mostacho
– Mosty Mostacho, Commented Apr 9, 2012 at 4:49
Can you explain what you want the query to Do? Guess time duration the user is logged in — Naveen Kumar
– Naveen Kumar, Commented Apr 9, 2012 at 5:31

cctan · Accepted Answer · 2012-04-09 03:04:47Z

1

SELECT user_id, sum(time_of_logon) as sum_login_time
FROM
(
  SELECT user_id,
         reference as r, 
         (time-(SELECT time FROM users_logs WHERE id = r)) as time_of_logon
  FROM users_logs 
  WHERE user_id = 1 AND action = 'logout'
) subtable
GROUP BY user_id

can you do a two step query?

edited Apr 9, 2012 at 3:04

answered Apr 9, 2012 at 2:55

cctan

2,0233 gold badges21 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kalreg Over a year ago

I could but i get error message: #1248 - Every derived table must have its own alias. I see it for the first time in my life

Bohemian · Accepted Answer · 2012-04-10 01:18:21Z

0

Just use a normal join:

select t1.id as r, t2.time - t1.time as time_of_logon
from user_logs t1
join user_logs t2 on t2.reference = t1.id
where t1.action = 'login'
and t1.user_id = 1;

No need to join on anything other than what's shown, because only logouts have a non-zero reference value. Actually, t1.action = 'login' is not necessary either for the same reason, but can be left in - it might help performance a bit (you should test it)

This will perform pretty well too if you have an index on reference.

edited Apr 10, 2012 at 1:18

answered Apr 9, 2012 at 4:53

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

2 Comments

Kalreg Over a year ago

Unfortunatel your example doesnt work. there is no 'r2' or r1' table. even if i change it to t2 and t1 i get 'no field named r2' in query error. I like elegancy of example.

Bohemian Over a year ago

OK I fixed the typos. Let me know if it works for you now. Cheers

Collectives™ on Stack Overflow

MYSQL - sum doesnt sum as intended

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related