计数MySQL中的空数据 [英] counting null data in MySQL

问题描述

let说我有一个表，有如下的数据：

 名称状态
 bob single 
 bob single 
 jane null 
 tina null 
 shane已婚
  

我想如果状态单或数据null这意味着单。所以如果数据空脚本可以读取为单个并且可以一起计数。
所以我可以显示结果像：

 单身已婚
 3 1 
  
 
 
 
我尝试过这个，它不工作：
  SELECT SUM（IF（status ='single'，1,0））AS Single，
 SUM（IF（status ='married'，1,0））AS已婚
 FROM table 
  
 
 
解决方案
使用：
  SELECT SUM（CASE WHEN x.status ='single'OR x.status IS NULL THEN 1 ELSE 0 END）AS single，
 SUM CASE WHEN x.status ='married'THEN 1 ELSE 0 END）结婚
 FROM（SELECT DISTINCT 
 t.name，
 t.status 
 FROM YOUR_TABLE t）x 
  
 
let say i have one table, which have data like:
name       status
bob        single
bob        single
jane       null
tina       null
shane      married
i want if status "single or data null" it means single. so if data empty script can read it as single and can count together.
so i can show result like:
Single     Married
3            1
i have tried with this and it doesnt work:
SELECT SUM(IF(status='single',1,0)) AS Single,
       SUM(IF(status='married',1,0)) AS Married
FROM table

解决方案
Use:
SELECT SUM(CASE WHEN x.status = 'single' OR x.status IS NULL THEN 1 ELSE 0 END) AS single,
       SUM(CASE WHEN x.status = 'married' THEN 1 ELSE 0 END) AS married
  FROM (SELECT DISTINCT
               t.name,
               t.status
          FROM YOUR_TABLE t) x


                        
这篇关于计数MySQL中的空数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！