[SOLVED] SUM in SQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ckuipers
Forum Commoner
Posts: 61
Joined: Mon Mar 24, 2003 6:10 am

SUM in SQL query

Post 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.
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post 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
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
User avatar
Etherguy
Forum Commoner
Posts: 70
Joined: Fri Nov 01, 2002 9:09 pm
Location: Long Island, New York

Post by Etherguy »

Lost,

You can not use the subtraction sign (-) in a subtime. A comma (,) would be the correct syntax!
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post by lostboy »

damn, my bad
ckuipers
Forum Commoner
Posts: 61
Joined: Mon Mar 24, 2003 6:10 am

Post 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.
Post Reply