CODEWITHSUNDEEP
sqljoincountinformix
gmeka
gmeka

Reputation: 4289

select total orders and last order from a customer

I wanted to find the total number of orders placed by a customer till date and the last order date.

Customer

custome_id      customer_name
    1              JOHN
    2              ALEX

Order

order_id       customer_id        order_date      status
   R1              1              06/06/2013      completed
   R2              1              05/29/2013      completed
   B1091           1              01/17/2011      canceled
   B2192           1              12/24/2010      completed

Note: order_id is not helpful to find last order as they are not incremental

The query which I am trying is 

select customer.customer_id, customer.customer_name, order.order_id as last_order_id, max(order.order_date) as maxOrderDate,
  sum( case when order.status='completed' then 1 else 0) as completed_orders,
  count( order_id) as total_orders 
  from customer as customer inner join order as order
  on customer.customer_id = order.customer_id
  where customer.id = 1
  group by customer.customer_id, customer.customer_name, order.order_id

Expecting results as 

 customer_id  customer_name  Last_order_id  maxOrderDate   completed_orders   total_orders
    1                JOHN        R1            06/06/2013             3              4

Upvotes: 2

Views: 2750

Answers (4)

Himanshu
Himanshu

Reputation: 32602

In case you want to get last Order_ID, you need to join order table with sub-query like this:

SELECT tbl.customer_id, tbl.customer_name, o.order_id,  MaxOrderDate, Completed_orders, Total_Order
FROM [ORDER] o
JOIN
( SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS MaxOrderDate
     ,SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS Completed_orders
     ,COUNT(order_id) AS Total_Order
   FROM Customer c 
   JOIN [Order] o
     ON c.customer_id = o.customer_id
  WHERE c.customer_id = 1
  GROUP BY c.customer_id,c.customer_name
) tbl
ON o.CUSTOMER_ID = tbl.CUSTOMER_ID
AND o.order_date = tbl.MaxOrderDate

Result:

╔═════════════╦═══════════════╦══════════╦══════════════╦══════════════════╦═════════════╗
║ CUSTOMER_ID ║ CUSTOMER_NAME ║ ORDER_ID ║ MAXORDERDATE ║ COMPLETED_ORDERS ║ TOTAL_ORDER ║
╠═════════════╬═══════════════╬══════════╬══════════════╬══════════════════╬═════════════╣
║           1 ║ JOHN          ║ R1       ║ 06/06/2013   ║                3 ║           4 ║
╚═════════════╩═══════════════╩══════════╩══════════════╩══════════════════╩═════════════╝

See this SQLFiddle

Upvotes: 2

sentil kumar
sentil kumar

Reputation: 95

Try this code and see.

SELECT c.customerId,c.customerName,MAX(o.orderDate) as MaxOrderDate,SUM(case when o.status='completed' then 1 else 0) as completed_orders,
COUNT(o.orderId) as total_order FROM customer c JOIN order o ON c.customerId=o.customerId
Where c.customerId=1 
group by c.customerId,c.customerName

Upvotes: 0

mirkobrankovic
mirkobrankovic

Reputation: 2347

SELECT o.order_id, o.customer_id, c.customer_name, MAX(o.order_date) 
FROM Order o
INNER JOIN Customer c ON c.custome_id = o.custome_id
WHERE o.status = 'completed'
GROUP BY o.customer_id
ORDER BY o.customer_id ASC;

Upvotes: 0

asafrob
asafrob

Reputation: 1858

select
customer_id,
MAX(order_date) as last_order_date,
count(*) as num_orders
from Order
group by customer_id

Upvotes: 0

Related Questions