CODEWITHSUNDEEP
phpmysqlsql
Clarus Dignus
Clarus Dignus

Reputation: 3897

Incorporate WHERE and GROUP BY into datetime sum MySQL query

My query:

I'm trying to sum the "duration" column by category:

SELECT DATE_FORMAT(FROM_UNIXTIME(sum(`duration`)), '%T') AS `totalduration` 
FROM `my_custom_table`
WHERE `category` = "SLE"
GROUP BY `category`

The result is NULL but should be "05:00".

If I exclude the WHERE and GROUP BY, the query sums correctly:

SELECT DATE_FORMAT(FROM_UNIXTIME(sum(`duration`)), '%T') AS `totalduration`
FROM `my_custom_table`

The result is correctly 24:00:00.

How do I incorporate WHERE and GROUP BY into my query?

My data:

A phpMyAdmin/MySQL table consisting of a "duration" column (datetime) and a "category" column (varchar 255):

0000-00-00 05:00:00 | SLE
0000-00-00 00:30:00 | CLA
0000-00-00 00:30:00 | CLA
0000-00-00 00:15:00 | FOO
0000-00-00 01:45:00 | MON
0000-00-00 00:30:00 | CLA
0000-00-00 01:30:00 | MON
0000-00-00 02:30:00 | CLE
0000-00-00 01:00:00 | CLA
0000-00-00 01:30:00 | MON
0000-00-00 01:00:00 | REC
0000-00-00 00:30:00 | CLA
0000-00-00 01:30:00 | MON
0000-00-00 00:45:00 | CLA
0000-00-00 01:00:00 | FOO
0000-00-00 01:00:00 | REC
0000-00-00 01:00:00 | REC
0000-00-00 01:00:00 | MON
0000-00-00 00:15:00 | NOT
0000-00-00 00:30:00 | CLA
0000-00-00 00:30:00 | CLA

Upvotes: 0

Views: 73

Answers (4)

AndrewShmig
AndrewShmig

Reputation: 4923

I have created a SQLFiddle for you: http://sqlfiddle.com/#!9/f31e6a/6/0

Query:

SELECT category, SEC_TO_TIME(SUM(TIME_TO_SEC(duration)))
FROM test
WHERE category = 'SLE'
GROUP BY category;

Test data:

CREATE TABLE test (duration DATETIME, category TEXT);

INSERT INTO test (duration, category) VALUES
('0000-00-00 05:00:00', 'SLE'),
('0000-00-00 00:30:00', 'CLA'),
('0000-00-00 00:30:00', 'CLA'),
('0000-00-00 00:15:00', 'FOO'),
('0000-00-00 01:45:00', 'MON'),
('0000-00-00 00:30:00', 'CLA'),
('0000-00-00 01:30:00', 'MON'),
('0000-00-00 02:30:00', 'CLE'),
('0000-00-00 01:00:00', 'CLA'),
('0000-00-00 01:30:00', 'MON'),
('0000-00-00 01:00:00', 'REC'),
('0000-00-00 00:30:00', 'CLA'),
('0000-00-00 01:30:00', 'MON'),
('0000-00-00 00:45:00', 'CLA'),
('0000-00-00 01:00:00', 'FOO'),
('0000-00-00 01:00:00', 'REC'),
('0000-00-00 01:00:00', 'REC'),
('0000-00-00 01:00:00', 'MON'),
('0000-00-00 00:15:00', 'NOT'),
('0000-00-00 00:30:00', 'CLA'),
('0000-00-00 00:30:00', 'CLA');

And corresponding results:

category    SEC_TO_TIME(SUM(TIME_TO_SEC(duration)))
SLE     05:00:00

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just use to_seconds():

select sum(to_seconds(duration))

If you want this in a time format:

select sec_to_time(sum(to_seconds(duration)))

Upvotes: 0

Mushfiqur Mashuk
Mushfiqur Mashuk

Reputation: 21

I think that there is no need to use group by because the where clause returns only one data.So you can do the following.

SELECT DATE_FORMAT(FROM_UNIXTIME(sum(`duration`)), '%T') AS `totalduration` 
FROM `my_custom_table`
WHERE `category` = "SLE"

If it still returns null,You can do the following.

SELECT DATE_FORMAT(FROM_UNIXTIME(sum(`duration`)), '%T') AS `totalduration` 
FROM `my_custom_table`
WHERE `category` LIKE "%SLE%"

Upvotes: 0

Racine Pilote
Racine Pilote

Reputation: 43

SELECT DATE_FORMAT(FROM_UNIXTIME(sum(`duration`)), '%T') AS `totalduration` 
FROM `my_custom_table`
GROUP BY `category` 
HAVING category = "SLE"

Upvotes: 0

Related Questions