Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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