CODEWITHSUNDEEP
mysqlsql
Martin AJ
Martin AJ

Reputation: 6697

How can I count matched rows individually?

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?

Upvotes: 1

Views: 59

Answers (3)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT 
 SUM(question_id IS NULL) a_num,
 SUM(question_id IS NOT NULL) q_num 
 FROM qanda 
 WHERE user_id = :user_id

Try above query.

As written in above query I had used question_id IS NULL it will generate 1 for true and 0 for false so SUM(question_id IS NOT NULL) will generate exact count of question.

Same is work for answer also.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 3

Matt
Matt

Reputation: 15061

Using a SUM with a CASE statement will accomplish this.

SELECT SUM(CASE WHEN question_id IS NULL 
                THEN 1
                ELSE 0
           END) AS q_num,
       SUM(CASE WHEN question_id IS NOT NULL 
                THEN 1
                ELSE 0
           END) AS a_num 
FROM qanda
WHERE user_id = :user_id

Output (For user_id = '213423')

q_num a_num
1     2

SQL Fiddle: http://sqlfiddle.com/#!9/4923c/2/0

Upvotes: 1

Related Questions