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.