samedi 27 février 2016

SQL customer-order query with order date ranges

I'm trying to get an order report returning not only a customers total number of orders, but also three additional fields, containing dates for first and last order, as well as the range (in days) between first and last order

The following query

SELECT customers.name, 
   customers.customer_id, 
   COUNT(orders.order_id) AS Orderscount 
   FROM customers 
   JOIN orders ON customers.customer_id = orders.customer_id 
   GROUP BY customers.name,
            customers.customer_id                       
         HAVING Orderscount >= 2  
         ORDER BY Orderscount DESC

returns a table with name, customer_id and the count of total number of orders.

Question is, how can one add to this query to also get the date for the first_order, last_order and the date range between the two?

I'm using SQLite.

Aucun commentaire:

Enregistrer un commentaire