How to select multiple counts 1

[skorda]

I need to write a select that counts fields for multiple conditions. I tried putting my conditions in the count function like this:

SELECT County, State, COUNT(!RSPFlag AND MailFlag) as AddrCount, COUNT(RSPFlag AND MailFlag) as AddrResp FROM csrProspects GROUP BY County, State ORDER BY State, County

But I kept getting the same results for each column (even though I know they don't match). Is there some way to do this in one query or do I have to combine a bunch of selects together? I am using VFP 8 (and 9 beta) by the way.

Sebastian

 

[baltman]

try SUMing the count.

 

[Stella740pl]

Try this:

SELECT County, State, ;
SUM(IIF(!RSPFlag AND MailFlag,1,0)) as AddrCount, ;
SUM(IIF(RSPFlag AND MailFlag,1,0)) as AddrResp ;
FROM csrProspects ;
GROUP BY County, State ;
ORDER BY State, County

 

[skorda]

Duh! It's been forever since I've done these kinds of counts. Thanks guys!

Sebastian


Stella740pl (Programmer) Nov 22, 2004
Try this:

SELECT County, State, ;
SUM(IIF(!RSPFlag AND MailFlag,1,0)) as AddrCount, ;
SUM(IIF(RSPFlag AND MailFlag,1,0)) as AddrResp ;
FROM csrProspects ;
GROUP BY County, State ;
ORDER BY State, County