user2095748 user2095748 - 2 months ago 8
MySQL Question

group multiple field data as one field in sql

My sql query

select status,count(id) from table group by status;


returns following Data

Resolved- 4
Closed - 12
Verified - 3
New* - 23
Unconfirmed* - 4
Needmoreinfo* - 5.


What i want mysql to return is

Resolved- 4
Closed - 12
Verified - 3
Found* - 32


i.e New,Unconfirmed,Needmoreinfo should be counted as 'Found'

Answer

your question is missing information to help you.. but maybe you want this

SELECT 
CASE WHEN (status NOT IN ('Resolved', 'Closed', 'Verified')) THEN 'Found*' ELSE status END as status, count(id) 
FROM table 
GROUP BY CASE WHEN (status NOT IN ('Resolved', 'Closed', 'Verified')) THEN 'Found*' ELSE status END