MySQL multiple counting

Question

I have the following table:

+----+----------+------------+
| id | drink    | gender     |
+----+----------+------------+
|  1 | Beer     | m          |
|  2 | Milk     | f          |
|  3 | Milk     | f          |
|  4 | Tea      | m          |
+----+----------+------------+

Now I want to count which gender prefers which drink. The result should look like this:

+-------+-------+--------+
| drink | fem   | male   |
+-------+-------+--------+
|  Beer | 0     | 1      |
|  Milk | 2     | 0      |
|  Tea  | 0     | 1      |
+----+----------+--------+

Has anyone an idea?

Thanks in advance for your suggestions.

John Woo · Accepted Answer · 2012-10-28 23:46:27Z

4

SELECT drink,
       SUM(CASE WHEN gender = 'f' THEN 1 ELSE 0 END) fem,
       SUM(CASE WHEN gender = 'm' THEN 1 ELSE 0 END) male
FROM tableName
GROUP BY drink

SQLFiddle Demo

the query above can be further be converted into prepared statement. This is helpful, if for instance, you manage to add another value for the gender (eg. u for unisex) without modifying the original query. example,

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when gender = ''',
      gender,
      ''' then 1 ELSE 0 end) AS ',
      gender
    )
  ) INTO @sql
FROM tableName;

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

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

SQLFiddle Demo

answered Oct 28, 2012 at 23:46

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

MySQL multiple counting

1 Answer 1

SQLFiddle Demo

SQLFiddle Demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related