CODEWITHSUNDEEP
mysqlsqltime
jomsk1e
jomsk1e

Reputation: 3625

Getting total elapsedTime (TIME) in SQL

Please take a look at my table:

enter image description here

I need to count the total 'code1' and total 'code2' and the total of combine code1 and code2 and the total elapsedTime per day (timefiledate). Please see this output I am getting:

enter image description here

Here's my query:

 SELECT 
        `Date`,
        `Code1`,
        `Code2`,
        `Total`
    FROM   
    (
        SELECT 
            timefiledate AS `Date`, 
            (SELECT COUNT(*) FROM tableName WHERE transactionCode= 'code1' AND timefiledate = `date`)AS code1,
            (SELECT COUNT(*) FROM tableName WHERE transactionCode= 'code2' AND timefiledate = `date`)AS code2, 
            (SELECT COUNT(*) FROM tableName WHERE transactionCode IN ('code1', 'code2') AND timefiledate = `date`)AS Total
            #Total elapsedTime goes here
        FROM bartran

    ) AS A

    WHERE `date` BETWEEN '2013-05-01' AND '2013-05-31'
    GROUP BY `date`;

Take note that elapsedTime type is 'TIME'. I am having trouble getting the total elapsedTime for the entire month. Please can someone give me an advise. Thanks a lot!

Upvotes: 0

Views: 383

Answers (2)

Valery Viktorovsky
Valery Viktorovsky

Reputation: 6726

It's possible to convert TIME to seconds:

SELECT 
        `Date`,
        `Code1`,
        `Code2`,
        `Total`,
        `TotalElapsedTime`
    FROM   
    (
        SELECT 
            timefiledate AS `Date`, 
            (SELECT COUNT(*) FROM tableName WHERE transactionCode= 'code1' AND timefiledate = `date`)AS code1,
            (SELECT COUNT(*) FROM tableName WHERE transactionCode= 'code2' AND timefiledate = `date`)AS code2, 
            (SELECT COUNT(*) FROM tableName WHERE transactionCode IN ('code1', 'code2') AND timefiledate = `date`)AS Total
            (SELECT SUM(TIME_TO_SEC(elapsedTime)) FROM tableName WHERE transactionCode IN ('code1', 'code2') AND timefiledate = `date`) AS TotalElapsedTime
        FROM bartran

    ) AS A

    WHERE `date` BETWEEN '2013-05-01' AND '2013-05-31'
    GROUP BY `date`;

Upvotes: 0

user359040
user359040

Reputation:

To return the total in seconds, use the time_to_sec function - like so:

SELECT sum(time_to_sec(elapsedTime))
FROM tableName 
WHERE transactionCode IN ('code1', 'code2') AND timefiledate = `date`

Upvotes: 1

Related Questions