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
You can use a composed table using unions to output all the rows you are expecting, then join this result with the real output
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') ;