CODEWITHSUNDEEP
sql
Geeknidas
Geeknidas

Reputation: 23

SQL: Need help with query construction

I am relatively new with sql and I need some help with some basic query construction.

Problem: To retrieve the number of orders and the customer id from a table based on a set of parameters.

I want to write a query to figure out the number of orders under each customer (Column: Customerid) along with the CustomerID where the number of orders should be greater or equal to 10 and the status of the order should be Active. Moreover, I also want to know the first transaction date of an order belonging to each customerid.

Table Description:

product_orders

Orderid CustomerId Transaction_date Status
------- ---------- ---------------- -------
 1       23         2-2-10          Active
 2       22         2-3-10          Active
 3       23         2-3-10          Deleted
 4       23         2-3-10          Active

Query that I have written:

select count(*), customerid
  from product_orders
 where status = 'Active'
 GROUP BY customerid
 ORDER BY customerid;

The above statement gives me

Ideal solutions should look like:

Total Orders CustomerID Transaction Date (the first transaction date)
------------ ---------- ----------------
11           23         1-2-10

Thanks in advance. I hope you guys would be kind enough to stop by and help me out.

Cheers,

Leonidas

Upvotes: 2

Views: 212

Answers (4)

Daniel Renshaw
Daniel Renshaw

Reputation: 34187

SELECT
    COUNT(*) AS [Total Orders],
    CustomerID,
    MIN(Transaction_date) AS [Transaction Date]
FROM product_orders
WHERE product_orders.Status = 'Active'
GROUP BY
    CustomerId
HAVING COUNT(*) >= 10

Upvotes: 1

Alex K.
Alex K.

Reputation: 175866

HAVING will allow you to filter aggregates like COUNT() & MIN() will show the first date.

select 
  count(*), 
  customerid, 
  MIN(order_date) 
from product_orders 
  where status = 'Active' 
  GROUP BY customerid
  HAVING COUNT(*) >= 10
  ORDER BY customerid

If you want the earliest date irrespective of status you can sub-query for it 

select 
  count(*), 
  customerid, 
  (SELECT min(order_date) FROM product_orders WHERE product_orders.customerid = p.customerid) AS FirstDate
from product_orders P 
  where status = 'Active' 
  GROUP BY customerid
  HAVING COUNT(*) >= 10
  ORDER BY customerid

Upvotes: 1

Salil
Salil

Reputation: 47512

select count(*), customerid,  MIN(Transaction_date) from product_orders
     where status = 'Active'
     GROUP BY customerid having  count(*) >= 10
     ORDER BY customerid

Upvotes: 0

codingbadger
codingbadger

Reputation: 44014

This query should give you the total active orders for each customer that has 10 or more active orders. It will also display the first active order date.

Select  Count(OrderId) as TotalOrders,
        CustomerId,
        Min(Transaction_Date) as FirstActiveOrder

From Product_Orders
Where [Status] = 'Active'
Group By CustomerId
Having Count(OrderId)>10

Upvotes: 0

Related Questions