Expressions with different Where clauses (1 Viewer)

[pickslides]

I am used to writing simple queries like

SELECT [Marketing].[Month], Sum(iif([Category]="Y",1,0))/Sum(iif([Category] is not null,1,0)) AS BookingSuccess
FROM [Marketing] 
WHERE [Batch]="1"

How can I have different WHERE clauses for different expressions?

I.e.

SELECT [Marketing].[Month], Sum(iif([Category]="Y",1,0))/Sum(iif([Category] is not null,1,0)) AS BookingSuccess
FROM [Marketing] 
WHERE [Batch]=[B]"1"[/B]
 
Sum(iif([Category]="Y",1,0))/Sum(iif([Category] is not null,1,0)) AS BookingSuccess
FROM [Marketing] 
WHERE [Batch]=[B]"2"[/B]

MQ

 

[JHB]

I don't get it, try to explain it again with some more details!

 

[plog]

First, your initial code isn't going to work: you need a GROUP BY clause. To accomplish what you want, you move the WHERE criteria to the field itself, by implementing it in an Iif statement (which you already have:

Sum(iif([Category]="Y" AND [Batch]="1",1,0))/Sum(iif([Category] is not null AND [Batch]="1",1,0)) AS Batch1BookingSuccess



That's the code for the first one, for the second just copy it and change the [Batch]=1 to [Batch]=2.

However, you have to stop there. You can't go [Batch]=3, [Batch]=4... Because at that point, its not worth it and you should just bring in the [Batch] field to the SELECT clause and GROUP BY it as well.