I want to count two columns, but need to get the results of first column in one row.
SELECT country, COUNT(*) FROM table1 GROUP BY country, type
This query gives me
country type COUNT(*) Canada first 22 Canada second 42 Canada third 15 Australia second 23 Australia third 18
but I need to get
country type_first type_second type_third Canada 22 42 15 Australia 23 18 0
as I want to update another table with these values, and with this row structure, I can update the country table row by row as I get from the above query.
UPDATE country SET first=x, second=x, third=x
type column is
ENUM with predefined values.