Naomi gave a solution (and a good one) to a VFP SQL question in Message #1608590

For the following table:

CREATE TABLE PCIDTest (PCID varchar(10), Qty int,  BarCode varchar(20), Available bit)

insert into pcidtest
values ('3U60LB987', 4, 'cIV3V00TCJ42', 1), 
         ('3U60LB987'  ,   4, 'cIV3V00TCJ43'  , 1),  
         ('3U60LB987'   ,   4, 'cIV3V00TCJ44'  , 1), 
         ('3U60LB987'    ,   4, 'cIV3V00TCJ45' , 1), 
         ('3U60LB987'     ,  4, 'cIV3V00TCJ46'   , 0), 
         ('3V60K0MGW'    , 1,  'cIV3V60K1GA0'   ,  1)

We wanted these results:

PCID             MaxQty	NumAvail     	NumNotAvail
3U60LB987 	4	4                  	1
3V60K0MGW	1	1	                   0

So basically, a conditional aggregation/count based on the Available bit flag

Her solution was this, to use an IIF to aggregate either a 1 or a 0. While SQL 2012 introduced an IIF, prior versions relied on a CASE statement. So here is an example of conditional aggregation, with her code modified to use a CASE.

select PCID, max(qty) AS Qty, sum( case when Available = 1 then 1 else 0 end) as NO_AVAIL,   
                          sum( case when Available = 0 then 1 else 0 end) as NO_NOTAVAIL
from PCIDTEST
GROUP BY PCId

Just to offer a 2nd approach (one with a more complicated execution plan, though performance appears to be the same on a table with a million test rows), I was curious about doing this with a few subqueries and a condition.

SELECT PCID, MAX(Qty) as MaxQty, 
         (SELECT COUNT(*) FROM PCIDTest Inside WHERE Inside.PCID = Outside.PCID AND Inside.Available = 1) AS NumAvail, 
        (SELECT COUNT(*) FROM PCIDTest Inside WHERE Inside.PCID = Outside.PCID AND Inside.Available = 0) AS NumNotAvail 
   FROM PCIDTest Outside
   GROUP BY PCID

I created an index on the PCID column and on PCID plus the Available flag, and the execution times in milliseconds are usually about the same.

Again, the original code with the inline IIF is fine - was curious about doing conditional aggregations using a subquery. Just a 2nd way to do it.