Mysql subquery for group and count records by specific field -

Mysql subquery for group and count records by specific field -

i have problem:

i have car table, each car record has state field, state field value can be:

1 = enable or 2 = disable

for example, in case need show cars grouped color field , counted color, not problem :)

here sql statement :

select `id`, `model_family`, `color`, count(`color`) 'quantity', `state` `auto` `model_family` = 'sedan' grouping `color` +-----+--------------+------------+----------+---+ | id | model_family | color | quantity | state | +-----+--------------+--------+----------+-------+ | 77 | sedan | reddish | 2 | 2 | | 42 | sedan | bluish | 3 | 2 | | 97 | sedan | greenish | 5 | 1 | +-----+--------------+--------+----------+-------+

results show two disabled records , one enabled record.

well, questions :

how can can show cars disabled , enabled example, if first grouped record if state disabled (state = 2) quantity field appear "0" (because not exists cars enabled) else quantity = n

something this:

+-----+--------------+------------+----------+-------+ | id | model_family | color | quantity | state | +-----+--------------+------------+----------+-------+ | 77 | sedan | reddish | 0 | 2 | | 42 | sedan | bluish | 0 | 2 | | 97 | sedan | greenish | 5 | 1 | +-----+--------------+------------+----------+-------+

regads !

if want disabled groups replaced 0, can set single case statement in select clause. this:

select id, model_family, color, (case when state = 1 count(*) else 0 end) quantity, state auto model_family = 'sedan' grouping color order id;

it tested fine info on sql fiddle.

edit note, since you're grouping color, can utilize count(*) instead of count(color) because going count same thing.

edit 2 of import note if color has enabled vehicles , disabled vehicles, still returns 0 because there @ to the lowest degree 1 disabled. if want count of enabled ones, can this:

select id, model_family, color, sum(case when state = 1 1 else 0 end) quantityenabled, state auto model_family = 'sedan' grouping color order id;

this fiddle has both examples.

mysql count subquery