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
from orders
group by orders.customerid
having sum(orders.totalvalue) > 100
) orderstotal
on customer.customerid=orderstotal.customerid
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.