By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

SQL - Total results

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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))

Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

P: n/a
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))

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.