Can I count two different things on the same query?

微信扫一扫,分享到朋友圈

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?

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

微信扫一扫,分享到朋友圈

Can I count two different things on the same query?

半汤乡学院:“乡村振兴战略下农村电商的今天与明天”——第二届半汤沙龙纪要

上一篇

Xiaomi wants to ship 100 million smartphones in 2018

下一篇

你也可能喜欢

Can I count two different things on the same query?

长按储存图像,分享给朋友