SQL – joining a table with an aggregated query

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.