GROUP BY two columns

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

NOTE: type column is ENUM with predefined values.

Looks like you want something like:

SELECT country
, sum(case when type = 'first' then 1 else 0 end) as type_first
, sum(case when type = 'second' then 1 else 0 end) as type_second
, sum(case when type = 'third' then 1 else 0 end) as type_third
FROM table1
GROUP BY country

StackExchange Database Administrators Q#28406, answer score: 5