mysql - mysql查询中单列的总时间

我希望在输出的底部添加一行，其中包含“duration”列的总数。
我的问题是：

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' );

这是我的输出：

+------+-------------------+------------+----------+----------+-----------------------+
| 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               |
+------+-------------------+------------+----------+----------+-----------------------+

我想要的是：

+------+-------------------+------------+----------+----------+-----------------------+
| 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    |                       |
+------+-------------------+------------+----------+----------+-----------------------+

02:25（总时长）是我要找的。

最佳答案

您可以使用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' )
    )