SQL: Need help building a query
I'm relatively new to sql and I need help with some basic query construction.
Problem . To get the order quantity and customer ID from a table based on a set of parameters.
I want to write a query to find out the number of orders under each customer (column: Customerid) along with the CustomerID, where the number of orders must be greater than or equal to 10 and the order status must be active. Moreover, I also want to know the first transaction date of the order belonging to each customer.
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
The request I wrote:
select count(*), customerid
from product_orders
where status = 'Active'
GROUP BY customerid
ORDER BY customerid;
The above statement gives me
- the sum of the entire order from the client id, but does not satisfy the condition of 10 orders.
- I don't know how to display the first date of the transaction along with the order under the customerid (status: can be active or deleted does not matter)
Ideal solutions should look like this:
Total Orders CustomerID Transaction Date (the first transaction date)
------------ ---------- ----------------
11 23 1-2-10
Thanks in advance. Hope you guys will be kind to come by and help me.
Greetings,
Leonidas
a source to share
HAVING
will allow you to filter aggregates of the type COUNT()
, and 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 regardless of the status, you can request 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
a source to share
This request should give you total active orders for each customer who 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
a source to share