mysql - 使用嵌套查询的mysql摘要列

Question

我需要一个 select 语句来返回不同的汇总列。

这是基本结构：

Column 1: Date
Column 2: Owner
Column 3: Sum of "Hours" where "Status" = 'billable'
Column 4: Sum of "Hours" where "Status" = 'non billable'

所以一个样本返回行看起来像......

Date | Owner | Billable | Non Billable
2019-01-01 Bob Smith 17.54 13.66
2019-01-01 Joe Johnson 23.17 16.54

这是我的代码。它返回整个日期范围内所有所有者的总小时数，而不是按日期的所有者。

SELECT "Date", "Owner",

(SELECT sum("Hours")
   FROM timesheets
  WHERE "Status" = 'Billable' AND "Date" between '2019-01-01' AND '2019-02-20'
) AS "Billable",

(SELECT sum("Hours")
   FROM timesheets
  WHERE "Status" = 'Non Billable' AND "Date" between '2019-01-01' AND '2019-02-20'
) AS "Non Billable"

 FROM timesheets
WHERE "Date" between '2019-01-01' AND '2019-02-20'
GROUP BY "Date", "Owner"
ORDER BY "Date", "Owner"

标签： mysql

Answer 1

您的子查询是按日期、所有者分组的，并且您有总和 .. 要获得按日期摸索的总和，所有者您可以使用条件总和直接求和

    SELECT Date, Owner, 
        sum(case when Status = 'Billable' then Hours else 0 end) `Billable`
        sum(case when Status = 'Non Billable' then Hours else 0 end) `Non Billable`
     FROM timesheets
    WHERE Date between '2019-01-01' AND '2019-02-20'
    GROUP BY Date, Owner
    ORDER BY Date, Owner