mysql - 如何计算有多少项目超出预算？

Question

我需要确定每个部门有多少项目超出了每小时预算。我已将其设置为将总工作时间减去最大分配时间，如果数字为正，则该项目超出预算。

显示为:

| Department | ProjectMaxHours | TotalHoursWorked | Balance |

| Marketing  |          135.00 |           160.00 |   25.00 |
| Finance    |          120.00 |            85.00 |  -35.00 |
| Accounting |          145.00 |           130.00 |  -15.00 |
| Marketing  |          150.00 |           165.00 |   15.00 |
| Finance    |          140.00 |            52.50 |  -87.50

我现在想从此 View 创建另一个 View ，其中列出标记有 2 个超支项目，财务有 0 个项目，会计有 0 个项目。

关于如何做到这一点有什么想法吗？

Answer 1

select   sum(cnt),
         Department
from     (select 0 as cnt,
                 Department
          from   [hours]
          where  Balance < 0
          union all
          select 1,
                 Department
          from   [hours]
          where  Balance > 0) as g
group by Department;