CODEWITHSUNDEEP
mysql
olk
olk

Reputation: 63

Counting multiple entries / statistics

I want to add a survey to a website. And a good survey needs a reporting. Some basic reports are done. Now I want to put some cream on the coffee ...

The table with sample data:

mysql> select * from u001;
+----+----------+------------+-------+---------------------+
| id | drink    | sex        | age   | date                |
+----+----------+------------+-------+---------------------+
|  1 | Beer     | m          | 30-39 | 2012-10-17 23:17:52 |
|  2 | Milk     | f          | 10-19 | 2012-10-18 00:15:59 |
|  3 | Milk     | f          | 20-29 | 2012-10-18 23:33:07 |
|  4 | Tea      | m          | 30-39 | 2012-10-20 22:47:08 |
|  5 | Water    | f          | 20-29 | 2012-10-20 22:47:30 |
|  6 | Milk     | m          | 30-39 | 2012-10-20 22:51:22 |
+----+----------+------------+-------+---------------------+
6 rows in set (0.00 sec)

I want to get a result that counts how many women/men likes Tea/Beer/etc. A desired result like this:

+-------+-----+---------+
| drink | sex | counted |
+-------+-----+---------+
| Beer  | m   | 1       |
| Milk  | f   | 2       |
| Tea   | m   | 1       |
| Water | f   | 1       |
| Milk  | m   | 1       |
+-------+-----+---------+

Have anyone some suggestions or solutions? Thanks in advance.

Upvotes: 3

Views: 311

Answers (2)

Francisco Soto
Francisco Soto

Reputation: 10392

select drink, sex, count(id) from u001 group by drink, sex

Upvotes: 2

John Woo
John Woo

Reputation: 263803

SELECT drink, sex, COUNT(id) counted
FROM   u001
GROUP BY drink, sex

SQLFiddle Demo

Upvotes: 5

Related Questions