0

I have a table with call records. Each call has a 'state' CALLSTART and CALLEND, and each call has a unique 'callid'. Also for each record there is a unique autoincrement 'id'. Each row has a MySQL TIMESTAMP field.

In a previous question I asked for a way to calculate the total of seconds of phone calls. This came to this SQL:

SELECT SUM(TIME_TO_SEC(differences)) FROM ( SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND,MIN(timestamp),MAX(timestamp)))as differences FROM table GROUP BY callid )x

Now I would like to know how to do this, only for callid's that also have a row with the state CONNECTED.

Screenshot of table: https://i.sstatic.net/nEnUJ.jpg

Improve this question
1
  • 1
    Add the sql tag for broader visibility. Commented Mar 22, 2014 at 20:14

1 Answer 1

Reset to default
2

Use a having clause:

SELECT SUM(difference) FROM (SELECT callid, TIMESTAMPDIFF(SECOND, MIN(timestamp), MAX(timestamp)) as difference FROM table GROUP BY callid HAVING SUM(state = 'Connected') > 0 ) c;

If you only want the difference in seconds, I simplified the calculation a bit.

EDIT: (for Mihai)

If you put in:

HAVING state in ('Connected')

Then the value of state comes from an arbitrary row for each callid. Not all the rows, just an arbitrary one. You might or might not get lucky. As a general rule, avoid using the MySQL extension that allows "bare" columns in the select and having clauses, unless you really use the feature intentionally and carefully.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks Gordon,you are a SQL machine.
@GordonLinoff, Why isn't it 'COUNT(state = 'Connected')' rather then 'SUM(state = 'Connected')'. i.e does "state = 'connected' return a numeric result? I am puzzled.
@RyanVincent . . . Because count() counts the number of non-NULL values. So, count(state = 'Connected') = count(state <> 'Connected') = count(state). That is, all three will count the number of non-NULL values in state.
@GordonLinoff, Thanks for reminding me about the 'here be dragons' with SQL group functions. It is a while since i have used them that intensely. I need to go and do more reading to remind myself about 'em.