Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 0 down vote accepted

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.

share|improve this answer

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

share|improve this answer

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
share|improve this answer

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().

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.
{"cc_re__cc_re__cc_by_sa_3_0": ["                  <li><a href=\"//area51.stackexchange.com\" title=\"proposing new sites in the Stack Exchange network\">Area 51</a></li>\r\n                        <li><a href=\"//careers.stackoverflow.com\">Stack Overflow Careers</a></li>\r\n                    \r\n                </ol>\r\n            </td>\r\n    </tr>\r\n</table>\r\n                </div>\r\n            </div>\r\n\r\n            <div id=\"copyright\">\r\n                site design / logo &#169; 2015 stack exchange inc; user contributions licensed under <a href=\"http://creativecommons.org/licenses/by-sa/3.0/\" rel=\"license\">cc by-sa 3.0</a> \r\n                with <a href=\"http://blog.stackoverflow.com/2009/06/attribution-required/\" rel=\"license\">attribution required</a>\r\n            </div>\r\n            <div id=\"svnrev\">\r\n                rev 2015.4.1.2434\r\n            </div>\r\n            \r\n        </div>\r\n    </div>\r\n    <noscript>\r\n        <div id=\"noscript-warning\">Stack Overflow works best with JavaScript enabled<img src=\"http://pixel.quantserve.com/pixel/p-c1rF4kxgLUzNc.gif\" alt=\"\" class=\"dno\"></div>\r\n    </nosc"], "copyright__copyright__copyright": [") to test for Null, but you can do that much more easily with IsNull() and Abs():\n\n  SELECT EmpID, Abs(Sum(Not IsNull([Value1]))) As Value1Count, \n     Abs(Sum(Not IsNull([Value2]))) As Value2Count\n  FROM MyTable\n  GROUP BY EmpID\n\nThat seems much simpler to me than using IIf().\n\nshare|improve this answer\n\nYour Answer\n\n\u00a0\ndiscard\n\nBy posting your answer, you agree to the privacy policy and terms of service.\n\nNot the answer you're looking for? Browse other questions tagged or ask your own question."]}