Re: SQL query to return duration at location
> Actually name, location and timestamp can be placed in different tables
?!?
Read again.
In your present design, whether a timestamp column value denotes coming or going depends on the timestamp value in some other row. You might design a spreadsheet that way, but it's a relational database design error; that's the "something basic wrong", unless you think it's good use of your time to write the row-to-row logic that your design would require for duration sums.
Once your table is redesigned as, for example, (name, location, came, went), and assuming the convention that missing data are represented as nulls, you can write something like ...
select name, location, sum( time_to_sec( timediff(went,came) ) ) as timeSpent
from logs
where !isnull(came) and !isnull(went)
group by name, location;
Edited 2 time(s). Last edit at 07/08/2015 04:23PM by Peter Brawley.