Count() Function Help

[DrewKull]

Ok ... so I'm looking at a query where im trying to show a bunch of
columns counted up based on criteria ...

So far i've been able to count all the rows up and show them ... and
also count up one column based on the criteria (value = yes) ... but
not both at the same time ...

When i try it with all of them, all of them are either the same number
or no numbers at all ...
So to get all the rows i just do

SELECT count(col1), count(col2), count(col3) from table;

And that give me ALL the row count ...

So to get a single column with the correct criteria i do:

SELECT count(col1) from table where col1='yes';

And this gives me the correct number im looking for, but for only 1 row
I've tried this with both AND and OR's:

SELECT count(col1), count(col2), count(col3) from table where col1=yes
AND/OR col2 = yes AND/OR etc ...

AND gives me '0' and OR gives me '12' and the cols should be 3,2,2
respectively. I understand the logic behind these wrong numbers, i
just dont know the logic to get the correct numbers ...

.... any ideas?!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the columns are truly Yes/No columns, their values should always be
zero or negative one (0 | -1). To count all the True (-1) columns use
the SUM() function like this:

SELECT ABS(SUM(col1)) AS col1_trues, ABS(SUM(col2)) As col2_trues, ...

To count all the False (0) columns use the SUM() function like this:

SELECT ABS(SUM(NOT col1)) AS col1_falses, ...

The expression (NOT col1) will evaluate to true (-1) if the column value
is False; it will evaluate to false (0) if the column value is True.
--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRPeNOoechKq OuFEgEQJq2QCeN1 5yjS4Ti48k3GuSQ 1cY81+RBpsAoMwA
91mUHgwATjXY6TN EbihETX7R
=jtcL
-----END PGP SIGNATURE-----
Dr******@gmail. com wrote:

Ok ... so I'm looking at a query where im trying to show a bunch of
columns counted up based on criteria ...

So far i've been able to count all the rows up and show them ... and
also count up one column based on the criteria (value = yes) ... but
not both at the same time ...

When i try it with all of them, all of them are either the same number
or no numbers at all ...
So to get all the rows i just do

SELECT count(col1), count(col2), count(col3) from table;

And that give me ALL the row count ...

So to get a single column with the correct criteria i do:

SELECT count(col1) from table where col1='yes';

And this gives me the correct number im looking for, but for only 1 row
I've tried this with both AND and OR's:

SELECT count(col1), count(col2), count(col3) from table where col1=yes
AND/OR col2 = yes AND/OR etc ...

AND gives me '0' and OR gives me '12' and the cols should be 3,2,2
respectively. I understand the logic behind these wrong numbers, i
just dont know the logic to get the correct numbers ...

.... any ideas?!

[DrewKull]

Damn dude ... life saver!!!
Totally forgot -1 and 0 would be recorgnized as yes or no!