How to divide result of one select query by another? (1 Viewer)

B

Broker666

Registered User.

Local time

Today, 15:57

Joined

Dec 13, 2012

Messages

26

• Mar 17, 2013

• #1

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

Retired

Local time

Today, 23:57

Joined

Jun 2, 2003

Messages

12,701

• Mar 17, 2013

• #2

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

 

B

Broker666

Registered User.

Local time

Today, 15:57

Joined

Dec 13, 2012

Messages

26

• Mar 17, 2013

• #3

Excellent, thanks Brian.

 

B

Broker666

Registered User.

Local time

Today, 15:57

Joined

Dec 13, 2012

Messages

26

• Mar 17, 2013

• #4

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

Retired

Local time

Today, 23:57

Joined

Jun 2, 2003

Messages

12,701

• Mar 18, 2013

• #5

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

Brian

 

MSAccessRookie

AWF VIP

Local time

Today, 18:57

Joined

May 2, 2008

Messages

3,428

• Mar 18, 2013

• #6

Broker666 said:

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.

Click to expand...

 

Brianwarnock

Retired

Local time

Today, 23:57

Joined

Jun 2, 2003

Messages

12,701

• Mar 18, 2013

• #7

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

Brian