CODEWITHSUNDEEP
mysqlsqlcount
Martin AJ
Martin AJ

Reputation: 6697

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?

Upvotes: 2

Views: 63

Answers (2)

Saleh Mosleh
Saleh Mosleh

Reputation: 544

You can use blow query:

select count(1) as count1  , sum(if(amount is not null,1,0)) as count2 from question_and_answers where type=0

count1 for count of (type=0) , count2 for count of (type=0 and amount is not null) . If you use sql server , use IIF instead of IF in query.

Upvotes: 1

juergen d
juergen d

Reputation: 204756

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

Upvotes: 3

Related Questions