Saturday, 23 April 2016

SQL Query : Join, grouping and condition for result on aggregate function


Problem : List number of purchase orders per customer.
                 Only display customers with 2 or more purchase orders.

  

Output the CUSTOMER_LASTNAME and PURCHASE_COUNT 
 


SQL   
SELECT c.lastname AS CUSTOMER_LASTNAME, po.count(*) AS PURCHASE_COUNT 
FROM customer c 
LEFT JOIN purchase_order po ON c.customer_id = po.customer_id
GROUP BY c.customer_id HAVING po.count(*) > 1


No comments:

Post a Comment

Note: only a member of this blog may post a comment.