Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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 at 18:34

Your Answer

 
discard

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.