MySQL: Case查询中的Case查询?

I have a query that gets unit count and it tagging if completed or not.

我有一个查询，它获取单元计数，如果完成了，它会进行标记。

SELECT distinct location, 
case when  id is NULL then 'Not Started'
 when '1'  then 'Completed' 
 else 'In Progress' end   as Remarks,
count(name) as CountName
FROM table
group by location,
case when  id is NULL then 'Not Started'
 when '1'  then 'Completed' 
 else 'In Progress' end;

Result:

结果:

But I want to summarized this as below image:

但我想总结如下图:

Condition is when there are more that two(2) Remarks in Location it should be tagged as "In-progress" and sum the CountName. But when only one Remarks for a Location, gets the Remarks as its tagged.

条件是当在位置上有两个以上的注释时，它应该被标记为“进行中”，并将计数名相加。但是，当一个地点只有一个评论时，就把评论作为它的标签。

3 个解决方案

#1

Like you said, a case within a case:

就像你说的，一个案例中的一个案例:

select location,
       case when count(distinct case when id is null then 'Not Started'
                                     when id = '1' then 'Completed'
                                     else 'In Progress' end) > 1
            then 'In Progress'
            else max(case when id is null then 'Not Started'
                          when id = '1' then 'Completed'
                          else 'In Progress' end)
        end as remarks,
        count(*) as CountName
  from tbl
 group by location

SQLFiddle Demo

SQLFiddle演示

#2

Not sure on this (sample data would help), but try this:

在这个问题上不确定(样本数据会有所帮助)，但是试试这个:

SELECT Location, 
    case when count(id) > 1 then 'In Progress'
         when max(id) is null then 'Not Started'
         when max(id) = 1 then 'Completed'
         else 'In Progress' end As Remarks,
    count(name) as CountName
FROM table
GROUP BY location

#3

Try This query:-

试试这个查询:-

SELECT location, 
       if(count(1) > 1, 'In Progress', Remarks) Remarks, 
       sum(countName) countName 
   FROM location 
   group by location

#1