[TECH] COUNTING ROWS IN A GROUP BY DIRECTIVE IN MYSQL
2009 March 06 | 0 commentsLet'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
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
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
Post a comment
All comments are held for moderation; basic HTML formatting is accepted.