QUERY HELP - SQL Server Forums

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

QUERY HELP

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2010-04-27 : 14:00:54

Hi

we have two tables customer and salesorder

we need to identify all customer that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100

please help below query is correct as per my requirement.

SELECT * FROM Customer
WHERE 100 > ALL(
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)

Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-27 : 14:10:28

maybe add SUM(OrderTotal) just in case someone like me ordered 1000 times of #2 pencil for 50cents each?

quote:
Originally posted by WoodHouse

Hi

we have two tables customer and salesorder

we need to identify all customer that have not yet made any purchases and those that have only made orders with an OrderTotal less than 100

please help below query is correct as per my requirement.

SELECT * FROM Customer
WHERE 100 > ALL(
SELECT OrderTotal FROM SalesOrder
WHERE Customer.CustomerID = SalesOrder.CustomerID)

Thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-27 : 14:48:36

Just to start with:

Declare @CustTable Table
(CustomerId int identity,
CustomerName Varchar(500)
)

Declare @SalesOrder TAble
(SOrderNo int identity,
CustomerId int,
OrderTotal int
)

Insert into @CustTable
Select 'OrderAbove100' union
Select 'OrderAbove500' union
Select 'OrderBelow100' union
Select 'NoOrder'

Insert into @SalesOrder
Select 1 , 70 union
Select 1 , 50 union
Select 2 , 550 union
Select 3 , 70

Select '0Order', CustomerId from @CustTable where CustomerId not in (
Select CustomerId from @SalesOrder)
Union
Select 'LessThan100', CustomerId from @CustTable CT where 100 > (
Select Sum(OrderTotal) from @SalesOrder SO where so.customerid = ct.customerid
group by so.CustomerId )

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 05:45:42

[code]
SELECT CustomerID,other reqd columns...
FROM
(
SELECT c.*,SUM(so.OrderTotal) OVER (PARTITION BY c.CustomerID) AS OrderTot
FROM Customer c
LEFT JOIN SalesOrder so
ON so.CustomerID = c.CustomerID
)t
WHERE OrderTot <=100
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 06:22:10

quote:

Originally posted by visakh16


SELECT CustomerID,other reqd columns...
FROM
(
SELECT c.*,SUM(so.OrderTotal) OVER (PARTITION BY c.CustomerID) AS OrderTot
FROM Customer c
LEFT JOIN SalesOrder so
ON so.CustomerID = c.CustomerID
)t
WHERE OrderTot <=100

OR OrderTot IS NULL

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 06:49:15

how can SUM be NULL?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 07:06:30

quote:
Originally posted by visakh16

how can SUM be NULL?

Because you're summing a column on the RHS of a left join. For any customers that do not have sales orders, SUM(so.OrderTotal) will return null.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:23:35

oh ok..I see now..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources