Karan Bhalla
Karan Bhalla

Reputation: 65

Total time of a single column in mysql query

I am looking to add a row at the bottom of my output that contains the total of the 'duration' column.

Here's my query:

SELECT d.id                                                     AS 'id', 
       d.NAME                                                   AS 'name', 
       a.start_time                                             AS 'start time', 
       a.end_time                                               AS 'end time', 
       Time_format(Timediff(a.end_time, a.start_time), '%H:%i') AS 'duration', 
       a.appointment_type                                       AS 
       'appointment type' 
FROM   demographic d 
       INNER JOIN appointment a 
               ON ( d.id = a.id ) 
WHERE  ( a.appointment_date BETWEEN 
         '2016-10-30 00:00:00' AND '2016-10-31 23:59:59' ); 

Here's my output:

+------+-------------------+------------+----------+----------+-----------------------+
| id   | name              | start time | end time | duration | appointment type      |
+------+-------------------+------------+----------+----------+-----------------------+
| 7615 | Ricky Fenson      | 15:45:00   | 16:14:00 | 00:29    | Phone Call            |
| 3329 | Keith Richards    | 11:30:00   | 11:59:00 | 00:29    | Drop In               |
|  455 | Mick Jagger       | 14:00:00   | 14:29:00 | 00:29    | Drop In               |
| 2346 | Brian Jones       | 10:00:00   | 10:29:00 | 00:29    | Drop In               |
| 3332 | Bill Wyman        | 11:00:00   | 11:29:00 | 00:29    | Drop In               |
+------+-------------------+------------+----------+----------+-----------------------+

What I would like is:

+------+-------------------+------------+----------+----------+-----------------------+
| id   | name              | start time | end time | duration | appointment type      |
+------+-------------------+------------+----------+----------+-----------------------+
| 7615 | Ricky Fenson      | 15:45:00   | 16:14:00 | 00:29    | Phone Call            |
| 3329 | Keith Richards    | 11:30:00   | 11:59:00 | 00:29    | Drop In               |
|  455 | Mick Jagger       | 14:00:00   | 14:29:00 | 00:29    | Drop In               |
| 2346 | Brian Jones       | 10:00:00   | 10:29:00 | 00:29    | Drop In               |
| 3332 | Bill Wyman        | 11:00:00   | 11:29:00 | 00:29    | Drop In               |
|      |                   |            |          | 02:25    |                       |
+------+-------------------+------------+----------+----------+-----------------------+

The 02:25 (total of duration) is what I'm after.

Upvotes: 1

Views: 42

Answers (1)

Oscar Gallardo
Oscar Gallardo

Reputation: 2708

You could use union:

    (SELECT d.id AS 'id', 
           d.NAME AS 'name', 
           a.start_time AS 'start time', 
           a.end_time AS 'end time', 
           Time_format(Timediff(a.end_time, a.start_time), '%H:%i') AS 'duration', 
           a.appointment_type AS 'appointment type' 
    FROM   demographic d 
    INNER JOIN appointment a ON ( d.id = a.id ) 
    WHERE  ( a.appointment_date BETWEEN 
             '2016-10-30 00:00:00' AND '2016-10-31 23:59:59' ) 
    )
    union
    (
    SELECT null AS 'id', 
           null AS 'name', 
           null AS 'start time', 
           null AS 'end time', 
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(a.end_time,a.start_time)))),'%H:%i') AS 'Duration' 
           null AS 'appointment type' 
    FROM   demographic d 
           INNER JOIN appointment a 
                   ON ( d.id = a.id ) 
    WHERE  ( a.appointment_date BETWEEN 
             '2016-10-30 00:00:00' AND '2016-10-31 23:59:59' )
    )

Upvotes: 2

Related Questions