Sometimes the query conditions are so complex that you can’t express them using plain WHERE clause with AND, OR and NOT. In this case, you need to use subqueries.
For instance, let’s calculate total lifetime count of paid orders for those countries that have at least one paid order made in September of 2012.
SELECT Cust.Country AS [Customer Country], COUNT(Ord.OrderID) AS [Order OrderID Count] FROM (Customers AS Cust RIGHT OUTER JOIN Orders AS Ord ON (Ord.CustomerID = Cust.CustomerID )) WHERE ( Cust.Country IN (SELECT CustSQ1.Country FROM (Customers AS CustSQ1 RIGHT OUTER JOIN Orders AS OrdSQ1 ON (OrdSQ1.CustomerID = CustSQ1.CustomerID )) WHERE ( OrdSQ1.OrderDate BETWEEN #2012-09-01# AND #2012-09-30# AND OrdSQ1.Paid = true ) ) AND Ord.Paid = true ) GROUP BY Cust.Country
Here “those countries that have at least one paid order made in September of 2012” condition is implemented as sub-query.
First, add columns - click on “[Add column]” link, select “Customer | Country” menu item, then - [Add column] -> “Order | OrderID”.
Now aggregate the OrderID column - click the Function button on the right side of the column and select “Count” menu item.
Add a condition with a subquery. Click “Add Condition”, select “Customer | Country” from the menu. Then change the operator - click on “is equal to” link and select “in subquery”. Then click “Edit subquery” link.
The sub-query editing dialog appears.
You need to select the single attribute of the same type as our “Customer | Country” attribute.
The “Conditions” area have the same user interface as regular EasyQuery. Here you add the following conditions:
“Order | Date” -> select “custom period” operator -> set start and end date via date picker
“Order | Paid” -> select “is true”.
Click “OK” button to submit the sub-query.
At last, add “Order | Paid is true” condition to the main query.