如果列的值为零,我如何从我的 SQL 查询中获取值? [英] How can I get values from my SQL query if the column has zero value?
问题描述
基本上我有一个训练数据集,但我希望它显示零而不是根本不显示
Basically i have a training dataset, but i want it to show zero instead of it not showing at all
以下节目都是不完整和完整的,但我希望它显示不完整,并且来自零不完整为 0 的部门
The following show's all incomplete and complete, but i want it show incomplete and from the departments that have zero incomplete as 0
这是我到目前为止所拥有的,我需要它来显示所有内容,我的同事试图帮助我但不得不离开,所以我问你们
This is what i have so far, i need it to show all, my coworker was trying to help me but had to leave, so i am asking you guys
Select distinct Department, Total FROM
(
Select Department, Total FROM
(
SELECT 'Incomplete' AS Status, department, count(*) as Total
FROM MyTable
WHERE CompletedTraining = 'Incomplete'
GROUP BY department
union all
Select 'Complete' AS Status, department, Count(*) as Total
FROM MyTable
WHERE CompletedTraining = ' Complete'
Group By Department
)
WHERE Status = 'Incomplete'
)
union all
SELECT DISTINCT Department, NULL AS Total
FROM MyTable
当我运行我的 python 脚本时,我得到了这个
When I run my python script i get this
Incomplete Training
[6, 8, 2, 3, 6, 4, 4, 5, 2, 4]
但是如果有人接受了我的培训,我希望它在列表中显示为零所以我的预期输出是这样的
but if someone took my training i want it to show zero in the list so my expected output would be something like this
Incomplete Training
[6, 8, 2, 3, 6, 4, 4, 5, 2, 4, 0, 0]
具有多个用户的示例文档 X 1500 行
Example document X 1500 rows with multiple users
Curriculum Name Department Employee Name Employee Email Employee Status Date Assigned Completion Date CompletedTraining
Security Training OIS John Doe john.doe@email.org Active 7/18/2017 21:47 8/2/2017 21:31 Complete
Support Training OIS Home Simpson home.simpson@email.gov Active 4/20/2017 15:33 5/3/2017 22:18 Complete
Security Training ASD bart simpson bart.simpson@email.gove Active 5/5/2017 20:22 11/30/2017 19:43 incomplete
Security Training CO jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training ECARS jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training EO jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training ISD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training MSCD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training RD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training TTD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training DP jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training MLD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training OIS jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training TTD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training TTD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
Security Training AQPSD jack johnson jack.johnson@email.gov Active 5/9/2017 21:15 5/10/2017 20:23 incomplete
推荐答案
这应该会让你得到不完整的#.
This should get you your incomplete #'s.
select distinct
Department
, (select count(*) from MyTable t2 where t2.Department = t1.Department and t2.CompletedTraining='Incomplete') as Total
from MyTable t1
在同一行获取未完成和完成
To get Incomplete and Completes on same row
select distinct
Department
, (select count(*) from MyTable t2 where t2.Department = t1.Department and t2.CompletedTraining='Incomplete') as TotalIncomplete
, (select count(*) from MyTable t2 where t2.Department = t1.Department and t2.CompletedTraining='Complete') as TotalComplete
from MyTable t1
或类似的东西
select distinct 'Incomplete' as Src
, Department
, (select count(*) from MyTable t2 where t2.Department = t1.Department and t2.CompletedTraining='Incomplete') as Total
from MyTable t1
union
select distinct 'Complete' as Src
, Department
, (select count(*) from MyTable t2 where t2.Department = t1.Department and t2.CompletedTraining='Completeomplete') as Total
from MyTable t1
这篇关于如果列的值为零,我如何从我的 SQL 查询中获取值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!