[TECH] COUNTING ROWS IN A GROUP BY DIRECTIVE IN MYSQL

2009 March 06

Let's say you have a mysql table that looks like this:

ID - Name
1 - AAA
2 - AAA
3 - BBB
4 - BBB
5 - CCC
6 - DDD

Now you want to count how often 'AAA', 'BBB', 'CCC' and 'DDD' appear in your table. You would use an SQL query like this:

SELECT DISTINCT name, COUNT(name) FROM table;

This will give you the following result:

name - COUNT(name)
AAA - 2
BBB - 2
CCC - 1
DDD - 1

Now, I haven't run the numbers on that, but I'm pretty sure this query - which gives you the exact same result - is faster:

SELECT DISTINCT name, SUM(1) FROM table;

;

EOF

Category: blog

Project Zomboid