mysql pivot table with concat AND pivot counting of selected rows -

i have following mysql-table cars layout

brand   | type  | color ----------------------- bmw     | e30   | red bmw     | e90   | blue bmw     | e12   | red audi    | a3    | green bmw     | e90   | red audi    | tt    | blue bmw     | e12   | blue audi    | a3    | green 

and want have pivot table result following layout:

brand_type  | red   | blue  | green | total  ------------------------------------------- bmw-e30     | 1     | 0     | 0     | 1 bmw-e90     | 1     | 1     | 0     | 2 bmw-e12     | 1     | 1     | 0     | 2 audi-a3     | 0     | 0     | 2     | 2 audi-tt     | 0     | 1     | 1     | 2 

the farest achive query:

select brand,      sum(if(color = 'red', 1, 0)) red,      sum(if(color = 'blue', 1, 0)) blue,      sum(if(color = 'green', 1,0)) green,     count(color) total `cars` group brand; 

with result:

brand   | red   | blue  | green | total  ------------------------------------------- bmw     | 3     | 2     | 0     | 5 audi    | 0     | 1     | 3     | 4 

i searched in net, not find solution 2 columns ( brand , type ) concated and used in pivot result

1. how can achive desired pivot table result? solved hint of @kevinsjöberg

select concat(brand, '-', type) bt,      sum(if(color = 'red', 1, 0)) red,      sum(if(color = 'blue', 1, 0)) blue,      sum(if(color = 'green', 1,0)) green,     count(color) total `cars` group bt; 

[removed second question, solved first answer already]

use concat function mysql.

concat(`brand`, '-', `type`) 

you group brand , type.

see http://sqlfiddle.com/#!9/6d3db/2