Count only if condition matches

  • Thread starter Thread starter Peter Carlson
  • Start date Start date
P

Peter Carlson

I need to do count(Actions2="34")...is this possible? Here is the query:

SELECT last, first, middle, count(IP.Actions1) AS total,
count(IP.Actions2)+total as Runs
FROM ((People AS P LEFT JOIN Incidents_Personnel AS IP ON
IP.Person=P.id ) LEFT JOIN xPersonnel_Status AS S ON P.status=S.id)
LEFT JOIN Incidents as I on I.id=IP.IncidentNumber
WHERE P.member=1 and S.active=1 and I.AlarmDate Between #1/1/2006# And
#12/31/2006#
GROUP BY last, first, middle
ORDER BY last, first, middle

I can't simply do it in the where clause, becuase I can't restrict the
collection of Actions1

Peter
 
I am not sure that I understand your table/query structure, but perhaps you
can use a subquery to do what you seek:

SELECT last, first, middle, count(IP.Actions1) AS total,
(SELECT T.Count(*) FROM IP AS T
WHERE T.Person = P.id AND T.Actions2="34") + total as Runs
FROM ((People AS P LEFT JOIN Incidents_Personnel AS IP ON
IP.Person=P.id ) LEFT JOIN xPersonnel_Status AS S ON P.status=S.id)
LEFT JOIN Incidents as I on I.id=IP.IncidentNumber
WHERE P.member=1 and S.active=1 and I.AlarmDate Between #1/1/2006# And
#12/31/2006#
GROUP BY last, first, middle
ORDER BY last, first, middle
 
Instead of count use sum to sum the number of true values in a boolean
expression. Since that is negative (True=-1) then use ABS to strip of the
negative sign.

Abs(Sum(IP.Actions2="34")) as CountActions2

Alternatives:
Count(IIF(IP.Actions2="34","X",Null) as CountActions2
Sum(IIF(IP.Actions2="34",1,Null)) as CountActions2


SELECT last, first, middle, count(IP.Actions1) AS total,
count(IP.Actions2)+total as Runs
FROM ((People AS P LEFT JOIN Incidents_Personnel AS IP ON
IP.Person=P.id ) LEFT JOIN xPersonnel_Status AS S ON P.status=S.id)
LEFT JOIN Incidents as I on I.id=IP.IncidentNumber
WHERE P.member=1 and S.active=1 and I.AlarmDate Between #1/1/2006# And
#12/31/2006#
GROUP BY last, first, middle
ORDER BY last, first, middle
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Monthly Select 1
problems with counting and summing records in a query 1
STILL NEED HELP COUNTING CONSECUTIVE RECORDS>=1 2
Help -- Is my there any way to not count duplicate values here? 5
Query trouble...summing count...suggestions? 1
Counting consecutive dates 5
SubQuery Assistance... 1
SIMPLE QUESTION MAYBE? need to count records BUT only records that 2

Back
Top