Recently we were asked how to join a table with an aggregated query from another table.
This is actually quite easy – you just embed the aggregated query from the second table into the second part of the query. An example is shown below.
select customer.*, orderstotal.totalsales
from customer join
(select orders.customerid, sum(orders.totalvalue) as totalsales
group by orders.customerid
having sum(orders.totalvalue) > 100
where customer.f7 = ‘France’
This will then return all records for customers from ‘France’, where the sales order total is greater than £100. The query will show all customer fields and their total sales value. A query of this type gives considerable flexibility. The join could also be changed to a left outer so that all customers are shown, even if they have no sales.