Here is my table structure:
-- qanda (stands for questions and answers)
+----+---------+-----------------------------------------------+--------------+-----------+------+
| id | title | content | question_id | user_id | type |
+----+---------+-----------------------------------------------+--------------+-----------+------+
| 1 | title1 | this is a question | NULL | 213423 | 0 |
| 2 | NULL | this is an answer | 1 | 435344 | 1 |
| 3 | NULL | this is another answer | 1 | 432435 | 1 |
| 4 | title2 | this is another question | NULL | 124324 | 0 |
| 5 | NULL | this is an answer for the second question | 4 | 213423 | 1 |
| 6 | NULL | this is another answer for the first question | 1 | 213423 | 1 |
+----+---------+-----------------------------------------------+--------------+-----------+------+
I want to count the number of question and answers individually. How can I do that?
The expected result for this user: :user_id = 213423
+--------+--------+
| q_num | a_num |
+--------+--------+
| 1 | 2 |
+--------+--------+
I can do that by two queries separately:
SELECT count(*) q_num FROM qanda WHERE user_id = :user_id AND question_id IS NULL
SELECT count(*) a_num FROM qanda WHERE user_id = :user_id AND question_id IS NOT NULL
Can I do that in one query?
You can just do:
SELECT count(questionid) as q_num,
sum(questionid is null) as a_num
FROM qanda
WHERE user_id = :user_id ;
count() with a column or expression counts the number of non-NULL values -- exactly what you want to do. MySQL treats boolean values as integers in a numeric context, with 1 for true and 0 for false.
You could also write this as:
(count(*) - count(questionid)) as a_num
or
sum(case when questionid is null then 1 else 0 end) as a_num
EDIT:
Using type, you can use a variant:
select sum(type = 0) as q_num, sum(type = 1) as a_num
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments