CODEWITHSUNDEEP
pythonsqlite
davidjbeiler
davidjbeiler

Reputation: 133

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

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

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]

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        [email protected]      Active  7/18/2017 21:47 8/2/2017 21:31                          Complete
Support Training    OIS Home Simpson    [email protected]  Active  4/20/2017 15:33 5/3/2017 22:18                          Complete
Security Training   ASD bart simpson    [email protected] Active  5/5/2017 20:22  11/30/2017 19:43                        incomplete
Security Training   CO  jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   ECARS    jack johnson   [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   EO  jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   ISD jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   MSCD    jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   RD  jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   TTD jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   DP  jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   MLD jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   OIS jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   TTD jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   TTD jack johnson    [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete
Security Training   AQPSD jack johnson  [email protected]  Active  5/9/2017 21:15  5/10/2017 20:23                         incomplete

Upvotes: 0

Views: 95

Answers (1)

TEEKAY
TEEKAY

Reputation: 1186

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

or something like

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

Upvotes: 2

Related Questions