SQL: Need help with query construction.
- by Geeknidas
Hi Guys,
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
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
