Urgent: Need help with query construction

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent: Need help with query construction

Author

Topic

geeknidas
Starting Member

5 Posts

Posted - 2010-04-29 : 05:23:14

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 customerID,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 of any order under a customer (status: could be active or delelted doesn't matter) within the same output

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 05:35:20

select
count(*) as TotalOrders,
Customerid,
min(Transaction_date) as Transactiondate
from product_orders
where status = 'Active'
GROUP BY customerid having count(*) >= 10
ORDER BY customerid

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 05:37:56

But this will give the min(Transaction_date) only from active orders - is that enough?

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 05:49:50

This should give you what you want:

SELECT t.TotalOrders, t.CustomerID, c.FirstTransactionDate
FROM (	SELECT COUNT(*) as TotalOrders, CustomerID
		FROM Product_Orders 
		WHERE Status = 'Active' 
		GROUP BY CustomerID 
		HAVING COUNT(*) >= 10 ) t
INNER JOIN (
		SELECT MIN(Transaction_date) as FirstTransactionDate, CustomerID
		FROM Product_Orders 
		GROUP BY CustomerID ) c
	ON c.CustomerID = t.CustomerID
ORDER BY CustomerID

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

geeknidas
Starting Member

5 Posts

Posted - 2010-04-29 : 05:53:12

Thanks a ton. That was really quick and was perfect.

Just one question - the where clause indicates to search in only active orders.

But there could be an instance where a customer has orderid associated with a deleted status and this is his first transaction in product_orders table.

Simply put, I want to know the first time he has interacted with my system (the order could now be deleted stage!)

Is this possible. sorry for bothering you

Cheers,
Leonidas

geeknidas
Starting Member

5 Posts

Posted - 2010-04-29 : 05:54:47

Ohh thanks DBA for helping me out..

This is exactly what I want. Let me trying executing this query.. thanks a ton guys.. Is there a way to add reps to your posts for helping me out?

Cheers,
leonidas

geeknidas
Starting Member

5 Posts

Posted - 2010-04-29 : 06:06:40

I am getting an error saying Ambigious column name CustomerID!

Any idea where I am going wrong

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 06:11:47

quote:
Originally posted by geeknidas

I am getting an error saying Ambigious column name CustomerID!

Any idea where I am going wrong

Oops. Sorry about that. I've edited the post. Try it now.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

geeknidas
Starting Member

5 Posts

Posted - 2010-04-29 : 06:24:19

Thank you DBA :D works like a charm.. thanks webfred. I have just started learning about joins and hence could not make out the silly error.

I really appreciate this. hope its okay to keep pestering the forum with such questions.

Thanks,
Leonidas

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-29 : 06:43:44

Please keep on pestering

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-29 : 07:11:52

quote:
Originally posted by geeknidas
hope its okay to keep pestering the forum with such questions.

That's what it's for. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources