Conditional sum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a number of fields that can either have 1 or 2. How can I sum just
the 1's and ignore the 2's.

The field names are from "p1q1A" to "p1q12A"

cheers
 
SELECT ABS((p1q1A=1) + ... + (p1q12A=1)) FROM tableName


In Jet, a comparison evaluates to true, -1, or false, 0 (or to NULL). So, we
just count the time it evaluates to true by adding the -1 and the 0, and
finally, take the absolute value, to remove the negative sign.

That expression also assumes that all the fields have a value, otherwise, a
NULL will occur in the result of the comparison and the whole sum will also
be evaluated to NULL. You can avoid this by:

SELECT ABS( (Nz(p1q1A, 0) =1) + .... (Nz(p1q12A, 0) = 1) ) FROM
tableName


Hoping it may help,
Vanderghast, Access MVP
 
Its worked, thanks


Michel Walsh said:
SELECT ABS((p1q1A=1) + ... + (p1q12A=1)) FROM tableName


In Jet, a comparison evaluates to true, -1, or false, 0 (or to NULL). So, we
just count the time it evaluates to true by adding the -1 and the 0, and
finally, take the absolute value, to remove the negative sign.

That expression also assumes that all the fields have a value, otherwise, a
NULL will occur in the result of the comparison and the whole sum will also
be evaluated to NULL. You can avoid this by:

SELECT ABS( (Nz(p1q1A, 0) =1) + .... (Nz(p1q12A, 0) = 1) ) FROM
tableName


Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top