CODEWITHSUNDEEP
mysqlsql
mr mithun
mr mithun

Reputation: 105

How to use count if in mysql

My sql query returns Syntax error near '== 1

my query is 

SELECT COUNT( IF ( application_type== 1 ) ) as total from table_1

Upvotes: 6

Views: 11357

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Don't use if() or case at all. MySQL conveniently treats booleans as integers, so you can simply do:

SELECT SUM( application_type = 1 ) as total 
FROM table_1;

Of course, if this is all you want, then use COUNT(*) and move the condition to a WHERE clause.

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

Your condition IF ( application_type== 1 ) is actually returning either TRUE or FALSE which is 1 OR 0. Now if you apply COUNT on a column contains 1 OR 0, it will always return you the total ROW count of the table. So you need to adjust your query. Anyway, using SUM in place of COUNT will serve your purpose I think-

SELECT SUM(IF( application_type = 1)) AS total from table_1

Upvotes: 0

Dia
Dia

Reputation: 41

Multiple issues with your syntax. This works:

SELECT COUNT(*) as total from table_1 WHERE application_type = 1;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

use case when

SELECT COUNT(case when application_type=1 then 1 end) as total from table_1

OR 

SELECT COUNT(*) as total from table_1
where application_type=1

OR

SELECT count(if(application_type=1, 1, NULL)) as total from table_1

Upvotes: 15

Related Questions