Get the Count of a multiple null Fields based on other Field Criteria with AND operator

[mrk777]

I have a table of Employee Details and I would like to identify the count of a few fields combinedly and identify the count to get the progress % at a Function level

Emp Table:

Emp ID	Function ID	Readiness Status	Time Frame	Criticality_Reason
1	2	Change Likely within 1 Year		Expert
2	2	Change Likely within 2 Years	< 2 Years	Main Source
3	1	Change Likely within 4 Years	<4 Years	Main Source
4	2		< 3 Years	Hard to find
5	3			Main source
6	3	Change Likely within 4 Years	<4 Years	Main Source
7	1	Change Likely within 3 Years	<3 Years	Hard to find

Along with that, I have a table with all the Function IDs along with Function Names as below:

Function_ID	Function_Name
1​	Accounts Payable
2​	F&A Excellence
3​	FP&A
4​	GL Accounting
5​	Revenue Operations
6​	Tax
7​	Treasury

Now I would like to use the QUERY and get the counts based on the blank fields from the Employee Table at a record level grouped at Function Level:

Function Name	Head Count	Blank Count Status
Accounts Payable	2	0
F&A Excellence	3	2 (as there are only 2 records with blanks related to that specific function)
FP&A	2	1 (as there is only one record with blanks for that specific function)

Note: the count has to be taken at a record level and if any of the highlighted fields are blank then that record needs to be treated as 1 and then combine at a Function Level based on Function Name/Function ID.

I also would like to calculate the progress like Head Count - Blank Count Status/ Head Count would give me the Progress %.

Note: If there are no blanks then it should show 100% and that means fields have been updated completely for the specific functions.

Please help!!

 

[plog]

First, 'blank' is an ambigous term. Fields can be NULL (without any data) and fields can be a zero length string (''). Which do you have? There is a difference.

I am going to assume yours are NULL. This SQL will derive all the counts you need from the Emp table. You can then use that query in another one to get the specific results in another query:

SELECT FunctionID, COUNT(FunctionID) AS TotalFunctions, SUM(iif(isnull(Status),1,0)) AS NullStati
FROM Emp
GROUP BY FunctionID

From there you can link in your Function table and then do your math.

 

[CJ_London]

use an aggregate query

and use sum rather than count for your readiness column

something like

SELECT FunctionID, count(*) as headcount, -sum(Readiness is not null) as BlankCount,  -sum(Readiness is not null)/count(*) as progress
FROM myTable
GROUP BY FunctionID
ORDER BY FunctionID

note the - before the sum, this is because True is -1

edit: much the same as Plog's

 

[mrk777]

NULL values must be analyzed from 3 fields Readiness Status; Time Frame; Criticality_Reason, not from only a single field.

I have mentioned the example data above for your reference. Thank you!

 

[plog]

Our methods can be expanded to accomplish that. Give it a shot. When you get stuck or it doesn't work exactly, post back here and explain what you have and what isn't working.