Reputation: 65
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
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