SQL: Need help with query construction

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

Problem: To r开发者_如何学Goetrieve 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

• the sum of all order under a customer id but does not fulfil the condition of atleast 10 orders.
• I donot know how to display the first transaction date along with the order under a customerid (status: could be active or delelted doesn't matter)

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

---

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

---

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

---

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

---

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