CODEWITHSUNDEEP
mysqlsql
Al Kasih
Al Kasih

Reputation: 886

MySQL: Count rows with conditional value

I want to know how much the user is login and how much they fail to and success to.

The category column is varchar (the stupidity of the ex programmer) while the value is only 1 or 0 only.

If 1 they success, if 0 it means fail. 

$loginForced = " SELECT
                    Count(categori) AS login,                       
                    Count(CASE WHEN categori = '1' THEN categori ELSE 0 END) AS success,                        
                    Count(CASE WHEN categori = '0' THEN categori ELSE 0 END) AS fail                        
                 FROM 
                    log
                 WHERE  email = '".$personal."' ";

Upvotes: 0

Views: 417

Answers (2)

potashin
potashin

Reputation: 44581

Use sum with condition:

$loginForced = "select count(`categori`) as `login`,                       
                       sum(`categori` = '1') as `success`,                        
                       sum(`categori` = '0') as `fail`                        
                from `log`
                where `email` = '".$personal."' ";

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You probably want t just skip the ELSE in the CASE expression:

SELECT Count(categori) AS login,                       
       Count(CASE WHEN categori = '1' THEN categori END) AS success,                        
       Count(CASE WHEN categori = '0' THEN categori END) AS fail                        
FROM log
WHERE  email = '".$personal."' ";

COUNT takes into account all non-null values, so using ELSE 0 is essentially pointless, since 0 is going to be counted just like any other value.

Upvotes: 1

Related Questions