Page 1 of 1
SUM in SQL query
Posted: Wed Jun 09, 2004 6:05 am
by ckuipers
I'm trying to sum a substraction in an sql query, but I don't get it to work.
Can any one tell me if the following works at all, or what I'm doing wrong:
Code: Select all
SELECT login_id, type_id, SUM(time_stop - time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
Was an error from my part, the above does work.
Posted: Wed Jun 09, 2004 9:07 am
by Etherguy
With out knowing what format your time_stop and time_start are... I willl assume it is in a standard format.... try this :
Code: Select all
SELECT login_id, type_id, SUBTIME(time_stop, time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
Posted: Wed Jun 09, 2004 9:09 am
by lostboy
Assuming you created the fields as TIME formats, you can't use sum....
try
Code: Select all
SELECT login_id, type_id, Subtime(time_stop - time_start) AS time_spent
FROM logs
WHERE country_id= $login_country_id
GROUP BY type_id, login_id
Posted: Wed Jun 09, 2004 9:14 am
by Etherguy
Lost,
You can not use the subtraction sign (-) in a subtime. A comma (,) would be the correct syntax!
Posted: Wed Jun 09, 2004 9:29 am
by lostboy
damn, my bad
Posted: Wed Jun 09, 2004 10:08 am
by ckuipers
Thx for that guys, but I found the problem, it was a typing error.
Either way, I always store my dates and times as unix time-stamps.