Martin AJ Martin AJ -4 years ago 103
MySQL Question

Can I count two different things on the same query?

I have a table like this:

// question_and_answers
+----+---------+---------------+--------+------+
| id | title | body | amount | type |
+----+---------+---------------+--------+------+
| 1 | t1 | b1 | NULL | 0 |
| 2 | t2 | b2 | NULL | 1 |
| 3 | t3 | b3 | NULL | 1 |
| 4 | t4 | b4 | 100 | 0 |
| 5 | t5 | b5 | NULL | 0 |
| 6 | t6 | b6 | NULL | 1 |
| 7 | t7 | b7 | 50 | 0 |
+----+---------+---------------+--------+------+


And I have two queries:

1: the number of questions:



SELECT count(1) FROM question_and_answers WHERE type = 0


2: the number of paid questions:



SELECT count(1) FROM question_and_answers WHERE type = 0 AND amount IS NOT NULL





Can I combine those two queries? I mean can I write one query instead of them?

Answer Source

You can use conditional aggregation:

SELECT sum(type = 0 AND amount IS NOT NULL),
       count(*) 
FROM question_and_answers 
WHERE type = 0

In MySQL the result of a comparision is 0 or 1. You can sum those results up like in the above query.

To make it work for other DB engines you could use this general ANSI SQL approach:

SELECT sum(case when type = 0 AND amount IS NOT NULL then 1 else 0 end),
       count(*)
FROM question_and_answers 
WHERE type = 0

or with count():

SELECT count(case when type = 0 AND amount IS NOT NULL then 1 else null end),
       count(*) 
FROM question_and_answers 
WHERE type = 0
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download