How can I set a criteria for SQL function?-CodePudding

I'm using the Microsoft Access to do the SQL and is there any way for me to set the criteria for the Functions such as COUNT?

I found that the COUNT() Function will count the records where the Captain field meets the requirement too where Pilots.[PilotNum] = Bookings.[Co-Captain] AND Pilots.[PilotNum] = Bookings.[Captain]

The SQL below is the SQL I used to find the number of the Co-captain for each Pilots

This is the SQL I used to find the number of the Co-captain for each Pilots

The result of my SQL

HOW can I use COUNT() function to count each PilotNum appeared in each column seperately

How?

CodePudding user response：

Instead of count() I think it would be easier to use sum(), like this:

Select Pilots.PilotNum
     , Sum(Iif(Bookings.[Co-Captain]=Pilots.PilotNum,1,0)) As CoCapt
     , Sum(Iif(Bookings.[Captain]=Pilots.PilotNum,1,0)) As Capt
From...

The ... just means the rest is the same as you wrote it. The key point here is to use the Iif() function to establish a one or zero value and then just sum them up.