Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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
share|improve this answer
    
This is more concise. +1 !!! –  RolandoMySQLDBA Nov 9 '12 at 0:53
    
This is exactly what I needed! –  All Nov 9 '12 at 1:02
add comment
SELECT
    country,
    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
FROM
(
    SELECT country, type, COUNT(*) country_type_count
    FROM table1 GROUP BY country, type
) A
GROUP BY country;
share|improve this answer
    
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
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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