[thelist] sql query for sum of timediff

[Matt Warden]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Canfield, Joel wrote:
> using mysql 4 on windows
> 
> (a/c is out, haven't slept in eight weeks, blah blah blah; brain
> !working)
> 
> table has, among others, columns firstname, lastname, starttime, endtime
> (start of call, end of call)
> 
> multiple entries differing only by starttime and endtime
> 
> I want something like
> 
>     select firstname, lastname, sum(timediff(endtime,starttime)) from
> presence where starttime between '2006-05-31 0:00:00' and '2006-05-31
> 23:59:59' group by ?????
> 
> as in, the total amount of time each person was on the phone for the
> given time period
> 
> can't seem to get the grouping correct; keep getting zero sum, or only
> one name

The thing I don't like about MySQL is that it lets you be sloppy with
group by.

Normally, if you do:

select foo, bar, sum(yadda)
from foobar

you would need to include:

group by foo, bar

because the resultset is being collapsed over foo and bar combinations
(i.e., we want the sum for each unique combo of foo and bar).

But with MySQL, it automatically adds that group by for you, if you
forget it. So, people who are used to MySQL don't get the rule of thumb
beaten into their heads: include in the group by any fields in the
select list which are not aggregate.

Thus, I believe you want:

select firstname, lastname, sum(timediff(endtime,starttime))
from presence
where starttime between '2006-05-31 0:00:00' and '2006-05-31 23:59:59'
group by firstname, lastname

You are going to get uninterpretable results if firstname+lastname
combos are not unique.


- --
Matt Warden
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEf3NCrI3LObhzHRMRAuGHAJ4ugFcGolh2jUBDCkJSERRhbeZboACdFTp8
+6Xfp3rbUxXr1mdYpNXqHDs=
=85/d
-----END PGP SIGNATURE-----