How to divide result of one select query by another?

[Broker666]

Hi,

I'm struggling with the correct syntax for this, but ideally what I want to achieve is:

SELECT (SELECT Count(Status) FROM P_T WHERE 'criteria are met') / (SELECT Count(Status) FROM P_T WHERE 'different criteria are met')

They are both fairly simplistic select statements, so I imagine this is not that difficult to achieve, but I'm not sure how to structure the query.

Thanks in advance for your help.

 

[Brianwarnock]

You don't need to bother with subqueries

Select Count(iif(status=firstcriteria,1) as expr1, Count(iif(status=secondcriteria,1) as expr2, expr1/expr2 as expr3
From P_T

or even

Select Count(iif(status=firstcriteria,1)/Count(iif(status=secondcriteria,1) as result
From P_T



Brian

 

[Broker666]

Excellent, thanks Brian.

 

[Broker666]

Do you see anything wrong with the syntax here?

Select Count(iif(Status = 'Sed - T+ 0',1) as expr1, Count(iif(status Is Not Null AND status <> 'Cancelled',1) as expr2, expr1/expr2 as expr3
From P_T
WHERE Bwer = Bwer = Forms!Stats!Combo2;

 

[Brianwarnock]

Both the Where clause and the first criteria look a bit odd

Brian

 

[MSAccessRookie]

Do you see anything wrong with the syntax here?

Select Count(iif(Status = 'Sed - T+ 0',1) ) as expr1, Count(iif(status Is Not Null AND status <> 'Cancelled',1) ) as expr2, expr1/expr2 as expr3
From P_T

WHERE Bwer = Bwer = Forms!Stats!Combo2;

WHERE Bwer = Forms!Stats!Combo2;[/QUOTE]

• Your first two parameters seem to have mismatched Parentheses. A suggested correction is displayed.
• The WHERE statement has Bwer = twice. I suspect this should have been only once.

 

[Brianwarnock]

Good catch on the brackets Rookie, but can't blame broker as I notice that I got it wrong on mine.

Brian