获取SQL中的总elapsedTime (时间)

Question

请看我的桌子：

​

我需要计算总的' code1‘和总的' code2’，以及组合的code1和code2的总数，以及每天的总elapsedTime (时间文件日期)。请看我得到的输出：

​

​

下面是我的问题：

 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`;

请注意，时间类型是‘elapsedTime’。我在获取整个月的elapsedTime时遇到了麻烦。有没有人能给我个建议。非常感谢!

Answer 1

要返回以秒为单位的总和，请使用time_to_sec函数，如下所示：

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

Answer 2

可以将时间转换为秒：

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`;