MySQL:用条件值计数行

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.

类别列是varchar(前程序员的愚蠢)，而值只有1或0。

If 1 they success, if 0 it means fail.

如果1他们成功，如果0意味着失败。

$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."' ";

2 个解决方案

#1

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."' ";

#2

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

你可能不想跳过案例表达式中的ELSE:

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.

COUNT考虑了所有非空值，因此使用ELSE 0本质上是没有意义的，因为0和其他任何值一样会被计数。

#1