1

Consider a table with a status field that holds one of two values:

| status | set('active','default') | YES  |  | active |

I need to know how many records are in the table, and additionally how many have the status set to active. I notice that SUM returns 1 for each record set to active and 2 for each record set to default:

mysql> select count(*), sum(status) from users;
+----------+-------------+
| count(*) | sum(status) |
+----------+-------------+
|        3 |           4 |
+----------+-------------+
1 row in set (0.00 sec)

mysql> select status from users;
+---------+
| status  |
+---------+
| default |
| active  |
| active  |
+---------+
3 rows in set (0.00 sec)

Can I rely on this behaviour? Is this a bad practice?

3

Answer to self!

A better way to perform the query in the OP in a single query would be to explicitly group by status:

mysql> SELECT COUNT(*) AS total, status FROM users GROUP BY status;
+-------+---------+
| total | status  |
+-------+---------+
|     2 | active  |
|     1 | default |
+-------+---------+
2 rows in set (0.00 sec)

In addition to being clearer as to intention, this solution also works for the case in which status has more than two possible values.

  • teh MySQL GROUP BY is a monster. Your answer shows the standards-compliant way of performing aggregates in a query and is the only way you should be doing it. – Max Vernon Mar 3 '14 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.