Counting multiple entries / statistics

Question

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.

score 5 · Accepted Answer · answered Oct 20 '12 at 22:45

5

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

SQLFiddle Demo

answered Oct 20 '12 at 22:45

John Woo

258,903
69
498
492

COUNT(*) is normally not a good idea because if there are covering indexes, the query will probably not use them. – Francisco Soto Oct 20 '12 at 22:47
@Francisco, Do you have a source for that? – Woutifier Oct 20 '12 at 22:50
[COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?](http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better) according on this post, it has no difference, `COUNT(*)` counts `null` while `COUNT(colName)` counts everythingexceptnull. – John Woo Oct 20 '12 at 22:53
1

I have no source and most probably the rdbms is smart enough to optimize that, but since you want to count the rows that belong to that group and there's a way to tell the different rows (the primary key) there's no need to specify everything is it? I stand corrected though. – Francisco Soto Oct 20 '12 at 23:01
I will run some tests locally to see exactly what does MySQL do. – Francisco Soto Oct 20 '12 at 23:02
Thank you very much guys for this really quick and helpful response! Maybe I couln'd see the forest for the trees ... – olk Oct 20 '12 at 23:04
@FranciscoSoto here's another [interesting blog about COUNT(*) vs COUNT(colName)](http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/) – John Woo Oct 20 '12 at 23:06

score 2 · Answer 2 · answered Oct 20 '12 at 22:46

2

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

answered Oct 20 '12 at 22:46

Francisco Soto

10,277
2
37
46

Counting multiple entries / statistics

2 Answers2

SQLFiddle Demo