How to get a row count if the value is not null?

Question

Using Access 2003

Table

EmpID Value1 Value2

001 100 
001       300
001 200   400
001 300   
001       250
001 200   400
002       100   
002 100   200
003 500   100
003
003 700
003

So on…,

I want to count (value1) and Count (value2) is not equal to null.

Expected Output.

EmpID Value1 Value2

001     4      4
002     1      2
003     2      1

So on…,

How to count the row value if it is not equal to null?

Need Query Help.

Answer 1

that seem to work?

SELECT Table1.Field1, Count(Table1.Field2) AS CountOfField2, Count(Table1.Field3) AS CountOfField3
FROM Table1
GROUP BY Table1.Field1;

and in your example, 003 is 2 and 1, not 1 and 0

result is:

 Field1 CountOfField2 CountOfField3
 1         4                4
 2         1                2
 3         2                1

which seem to be valid if field are NULL

Answer 2

I think I understand, but you may need to tweek this a bit... The Access database engine's IIF() function does a test, if TRUE, return value 1, otherwise return value 0.

select 
      EmpID, 
      sum( IIF( IsNull( Value1 ), 0, 1 )) as Fld1WithValues, 
      sum( IIF( IsNull( Value2 ), 0, 1 )) as Fld2WithValues 
   from 
      Table1 
   group by 
      EmpID;

So, even though you may have 6 entries for EmpID 1, but 2 are NULL, by doing a SUM() of either a 0 or 1, all records get counted for each field respectively and nothing is lost in the case the either/or values of Value 1 or Value 2 being null. They are tested on their OWN values.

Answer 3

If you want to go across the entire table

SELECT
    SUM (IIF(value1 IS NULL, 0, 1)) as NumNonNullValue1,
    SUM (IIF(value2 IS NULL, 0, 1)) as NumNonNullValue2

FROM Employee

but if you want Non Nulls per employee id then

SELECT
    EmpID,
    SUM (IIF(value1 IS NULL, 0, 1)) as NumNonNullValue1,
    SUM (IIF(value2 IS NULL, 0, 1)) as NumNonNullValue2

FROM Employee

GROUP BY EmpID

of course, sometimes just by EmpId is useful for querying, but not for display. You need the name, so we add the name field(s)

SELECT
    EmpID,
    EmployeeName,
    SUM (IIF(value1 IS NULL, 0, 1)) END as NumNonNullValue1,
    SUM (IIF(value2 IS NULL, 0, 1)) END as NumNonNullValue2

FROM Employee

GROUP BY EmpID, EmpoyeeName

Answer 4

This is an Access-specific answer, but that's the context of the original question. You want to count the incidence of non-Null values, not the number of records. Several answers have used IIf() to test for Null, but you can do that much more easily with IsNull() and Abs():

  SELECT EmpID, Abs(Sum(Not IsNull([Value1]))) As Value1Count, 
     Abs(Sum(Not IsNull([Value2]))) As Value2Count
  FROM MyTable
  GROUP BY EmpID

That seems much simpler to me than using IIf().