MYSQL: count column if something if another column is equal to something else

mysql, sql

Solution

SELECT  Country_Code,
        SUM(CASE WHEN vote = 'like' THEN 1 ELSE 0 END) `like`,
        SUM(CASE WHEN vote = 'dislike' THEN 1 ELSE 0 END) dislike
FROM tableName
GROUP BY Country_Code

- SQLFiddle Demo

or if you want `PreparedStatement` (both give the same results)

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN vote = ''',
      Vote,
      ''' then 1 Else 0 end) AS `',
      Vote, '`'
    )
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  Country_Code, ', @sql, ' 
                   FROM tableName
                   GROUP BY Country_Code');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

- SQLFiddle Demo

Problem

I have the following table: ``` ---------------------------------------------- |ID|Vote_Item_ID|User_ID|Country_code| Vote| |01| 105102151|user1 | CA| like| |02| 105102151|user2 | CA|dislike| |03| 105102151|user3 | UK|dislike| |04| 105102151|user4 | UK| like| |05| 105102151|user5 | UK| like| ---------------------------------------------- ``` What I need to do is create an SQL statement that creates an array which totals the likes and dislikes for each country...The script I am using this with has 175 countries, so would this be an inefficient way to about it? I'm not sure how to go about writing the Select statement, since I want the script to be reusable for many different "vote_item_id"s I am using PDO with a MYSQL database by the way. Thanks

Original source