mysql - mysql中总和总是返回1

Question

我正在连接两个表 task 和 taskdetail

select t.task_id,td.task_id,td.task_detail_id,td.status 
from task t , task_detail td 
where td.task_id=t.task_id and td.status='ACTIVE'

返回 5 条记录。但是对于 task id 5 有两个 taskdetail 是事件的所以我想有另一列作为 totalactive 它应该具有所有 的值总和code>ACTIVE 针对任务。为此，我添加了:

SUM(CASE WHEN sd.status='ACTIVE' THEN 1 else 0 END) as totalactive and ``GROUP BY td.task_detail_id

但它在所有情况下都返回 1。

Result

+---+---------+---------+----------------+----------+-------------+
|No | task_id | task_id | task_detail_id |  status  | totalactive |
+---+---------+---------+----------------+----------+-------------+
| 1 |    2    |    2    |       3        |  ACTIVE  |      1      |
| 2 |    3    |    3    |       5        |  ACTIVE  |      1      |
| 3 |    4    |    4    |       6        |  ACTIVE  |      1      |
| 4 |    5    |    5    |       8        |  ACTIVE  |      1      |
| 5 |    5    |    5    |       9        |  ACTIVE  |      1      |
+---+---------+---------+----------------+----------+-------------+

因为有 8,9 两个 task detail 针对单个任务 5 我想在最后一行都有计数 2

Answer 1

您需要按td.task_id 或t.task_id 分组>

select t.task_id, td.task_id, td.task_detail_id, count(1) as totalactive
  from task t 
  join task_detail td on td.task_id=t.task_id 
 where td.status='ACTIVE'
 group by td.task_id

或

select t.task_id, td.task_id, td.task_detail_id,
       sum(case when td.status='ACTIVE' then 1 else 0 end) as totalactive
  from task t 
  join task_detail td on td.task_id=t.task_id      
 group by td.task_id

不要为每个 task_detail_id 单独计数

Demo