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
  • This is more concise. +1 !!! – RolandoMySQLDBA Nov 9 '12 at 0:53
  • This is exactly what I needed! – Googlebot Nov 9 '12 at 1:02
    SUM(IF(A.type='first' ,country_type_count,0)) type_first,
    SUM(IF(A.type='second',country_type_count,0)) type_second,
    SUM(IF(A.type='third' ,country_type_count,0)) type_third
    SELECT country, type, COUNT(*) country_type_count
    FROM table1 GROUP BY country, type
) A
GROUP BY country;
  • 1
    If you do the group by and count(*) in the subquery...you can't then SUM with IF... You'd end up with a country and a sum of 1 for each type. – rfusca Nov 9 '12 at 0:50
  • @rfusca You are right. I forget my GROUP BY. – RolandoMySQLDBA Nov 9 '12 at 0:52
  • 1
    If you're going to do it like that, you'd have to do SUM(if(A.type='first',country_type_count,0)) and such. – rfusca Nov 9 '12 at 0:54
  • @rfusca I was editing it as you commented. Thanks you the additional catch :-) – RolandoMySQLDBA Nov 9 '12 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.