Order by FIELD in MYSQL
mysql, mysql-workbench
Solution
how abt this:
select a.col as type,coalesce (`COUNT`,0) as `count`
from
(select 'A' as col union all
select 'B' as col union all
select 'C' as col union all
select 'D' as col )a
left join Table1 T
on a.col=T.type
order by FIELD(a.col,'A','B','C','D') ;
SQL fiddle demo
Problem
I am struggling to resolve this . I have a table like this . ``` +-------------+-------+ | type | COUNT | +-------------+-------+ | A | 1 | | C | 5 | | B | 4 | +-------------+-------+ ``` I want to query the table and the result must be like this . ``` +-------------+-------+ | type | COUNT | +-------------+-------+ | A | 1 | | B | 5 | | C | 9 | | D | 0 | +-------------+-------+ ``` QUERY: ``` select type , COUNT from TABLE order by FIELD(type,'A','B','C','D') ; ``` It works fine if the column `type` has value for 'A,B,C,D' . In some cases the order by `FIELD('A','B','C','D')` some columns may not have value in table . In this cases I want to put 0 for it and construct a result . D is not there in table . So put '0' for it . SHOW CREATE TABLE OUTPUT ``` CREATE TABLE `Summary` ( `TIMESTAMP` bigint(20) NOT NULL DEFAULT '0', `type` varchar(50) NOT NULL DEFAULT '', `COUNT` bigint(19) NOT NULL, PRIMARY KEY (`TIMESTAMP`,`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ```