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

Question

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

I know the answer to the other question - you don't need to create JS using PHP at all. — Popnoodles
– Popnoodles, Commented Sep 9, 2014 at 18:19

John Woo · Accepted Answer · 2012-11-27 15:38:16Z

14

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

answered Nov 27, 2012 at 15:38

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Grijesh Chauhan Over a year ago

you made like as 'like' but not dislike... When its necessary?

John Woo Over a year ago

@GrijeshChauhan because LIKE is a Reserved Word.

Grijesh Chauhan Over a year ago

got it! I am new to MySQL ... Even I have some unanswered questions, and waiting for answers.

Owen Over a year ago

@Grijesh, you have to wait a few minutes before accepting answers on stackoverflow, I will when the time expires

bonCodigo · Accepted Answer · 2012-11-27 16:01:53Z

1

If you had shown your expected output, that would have been great. Following sample is simple. So let us know what you really need beyond this. Would be happy to pitch in. :)

REFERENCE : SQLFIDDLE

CODE:

   SELECT t.country_code,
         sum(CASE WHEN t.vote = 'like' 
               THEN 1 ELSE 0 END) AS LIKES,
         sum(CASE WHEN t.vote = 'dislike' 
               THEN 1 ELSE 0 END) AS DISLIKE         
   FROM tbl t
   GROUP BY t.country_code
  ;

RESULTS:

COUNTRY_CODE    LIKES   DISLIKE
CA                1     1
UK                2     1

answered Nov 27, 2012 at 16:01

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related