fLen fLen - 4 months ago 13
MySQL Question

MySQL: Case Query within a Case Query?

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:

enter image description here

But I want to summarized this as below image:

enter image description here

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.

Answer

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