[Tech] Counting rows in a GROUP BY directive in MySQL

Why technology sucks, and some just sucks less. The view and opinion of an experienced user.

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

Allowed HTML tags: a abbr acronym b blockquote em li ol p strong sub sup u ul

The TechSucks TechBlog - blog.crash-override.net