SQL - Total results

[Darryl Kerkeslager]

I'm trying to construct a simple query (Hah! then why can't I figure it out
if it's so simple?) that totals the results where the drug test results are
True, negative being treated like any other result. However, instead of
giving only the count of the True results for each result, I'm getting the
count of the tests, regardless of result: a person taking three drug tests,
all negative, will display:

offender_id off_name Neg Coc
233000 Dough, John Boy 3 3

Not

233000 Dough, John Boy 3 0
Here is my (wrong) SQL:

SELECT offender_id, off_name,
Count(drug_test.dt_negative=True) AS Neg,
Count(drug_test.dt_cocaine=True) AS Coc
FROM offender
INNER JOIN drug_test
ON offender.offender_id = drug_test.dt_off_id
GROUP BY offender.offender_id, offender.off_name
Darryl Kerkeslager

[Trevor Best]

Darryl Kerkeslager wrote:

I'm trying to construct a simple query (Hah! then why can't I figure it out
if it's so simple?) that totals the results where the drug test results are
True, negative being treated like any other result. However, instead of
giving only the count of the True results for each result, I'm getting the
count of the tests, regardless of result: a person taking three drug tests,
all negative, will display:

offender_id off_name Neg Coc
233000 Dough, John Boy 3 3

Not

233000 Dough, John Boy 3 0
Here is my (wrong) SQL:

SELECT offender_id, off_name,
Count(drug_test.dt_negative=True) AS Neg,
Count(drug_test.dt_cocaine=True) AS Coc
FROM offender
INNER JOIN drug_test
ON offender.offender_id = drug_test.dt_off_id
GROUP BY offender.offender_id, offender.off_name
Darryl Kerkeslager

Try Sum(Abs(drug_test.dt_cocaine=True))

--
This sig left intentionally blank

[Darryl Kerkeslager]

Yup, that did it. Thanks. Makes perfect sense in hindsight, but I'm glad I
asked, because I would have beat my head black and blue trying to come up
with that solution on my own.
Darryl Kerkeslager

"Trevor Best" <no****@besty.org.uk> wrote:

Darryl Kerkeslager wrote:

Here is my (wrong) SQL:

SELECT offender_id, off_name,
Count(drug_test.dt_negative=True) AS Neg,
Count(drug_test.dt_cocaine=True) AS Coc
FROM offender
INNER JOIN drug_test
ON offender.offender_id = drug_test.dt_off_id
GROUP BY offender.offender_id, offender.off_name

Try Sum(Abs(drug_test.dt_cocaine=True))

[Trevor Best]

Darryl Kerkeslager wrote:

Yup, that did it. Thanks. Makes perfect sense in hindsight, but I'm glad I
asked, because I would have beat my head black and blue trying to come up
with that solution on my own.

You can get a bit more efficient if drug_test.dt_cocaine is a boolean
field type then:

Sum(Abs(drug_test.dt_cocaine))

or even

Abs(Sum(drug_test.dt_cocaine))
--
This sig left intentionally blank

[Darryl Kerkeslager]

The table actually contains eight Boolean fields, for specific drug tests,
no shows, and negative results, so the solution was used eight times:)
Darryl Kerkeslager

"Trevor Best" <no****@besty.org.uk> wrote:

Darryl Kerkeslager wrote:

Yup, that did it. Thanks. Makes perfect sense in hindsight, but I'm glad I asked, because I would have beat my head black and blue trying to come up with that solution on my own.

You can get a bit more efficient if drug_test.dt_cocaine is a boolean
field type then:

Sum(Abs(drug_test.dt_cocaine))

or even

Abs(Sum(drug_test.dt_cocaine))