HAVING CLAUSE - 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

HAVING CLAUSE

Author

Topic

jtsroberts
Starting Member

2 Posts

Posted - 2008-10-08 : 08:38:00

I would like to find which customer have placed an order whose total is greater than the average order total.
Any ideas on how I need to format the 'HAVING' clause?
Thanks

SELECT Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrder.PurchaseOrderID,
SUM(CustomerPurchaseOrderItem.Quantity * CustomerPurchaseOrderItem.LineTotal) AS 'Order Total'
FROM CustomerPurchaseOrderItem
JOIN Part ON Part.PartID = CustomerPurchaseOrderItem.PartID
JOIN CustomerPurchaseOrder ON CustomerPurchaseOrder.PurchaseOrderID = CustomerPurchaseOrderItem.PurchaseOrderID
JOIN Customer ON Customer.CustomerID = CustomerPurchaseOrder.CustomerID
JOIN Person ON Person.PersonID = Customer.CustomerID
GROUP BY CustomerPurchaseOrder.PurchaseOrderID, Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrderItem.Quantity, CustomerPurchaseOrderItem.LineTotal
HAVING SUM(CustomerPurchaseOrderItem.Quantity*CustomerPurchaseOrderItem.LineTotal) > AVG(SELECT SUM(CustomerPurchaseOrderItem.Quantity*CustomerPurchaseOrderItem.LineTotal)
FROM CustomerPurchaseOrderItem
GROUP BY CustomerPurchaseOrderItem.PurchaseOrderID);

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-08 : 08:59:45

SELECT Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrder.PurchaseOrderID,
SUM(CustomerPurchaseOrderItem.Quantity * CustomerPurchaseOrderItem.LineTotal) AS 'Order Total'
FROM CustomerPurchaseOrderItem
JOIN Part ON Part.PartID = CustomerPurchaseOrderItem.PartID
JOIN CustomerPurchaseOrder ON CustomerPurchaseOrder.PurchaseOrderID = CustomerPurchaseOrderItem.PurchaseOrderID
JOIN Customer ON Customer.CustomerID = CustomerPurchaseOrder.CustomerID
JOIN Person ON Person.PersonID = Customer.CustomerID
GROUP BY CustomerPurchaseOrder.PurchaseOrderID, Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrderItem.Quantity, CustomerPurchaseOrderItem.LineTotal
HAVING SUM(CustomerPurchaseOrderItem.Quantity*CustomerPurchaseOrderItem.LineTotal) > (SELECT AVG(CustomerPurchaseOrderItem.Quantity*CustomerPurchaseOrderItem.LineTotal)
FROM CustomerPurchaseOrderItem
GROUP BY CustomerPurchaseOrderItem.PurchaseOrderID)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-08 : 09:06:19

Ignore that please.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-08 : 09:15:48

SELECT Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrder.PurchaseOrderID,
SUM(CustomerPurchaseOrderItem.Quantity * CustomerPurchaseOrderItem.LineTotal) AS 'Order Total'
FROM CustomerPurchaseOrderItem
JOIN Part ON Part.PartID = CustomerPurchaseOrderItem.PartID
JOIN CustomerPurchaseOrder ON CustomerPurchaseOrder.PurchaseOrderID = CustomerPurchaseOrderItem.PurchaseOrderID
JOIN Customer ON Customer.CustomerID = CustomerPurchaseOrder.CustomerID
JOIN Person ON Person.PersonID = Customer.CustomerID
GROUP BY CustomerPurchaseOrder.PurchaseOrderID, Customer.CustomerID, Person.FirstName, Person.LastName, Person.Email, CustomerPurchaseOrderItem.Quantity, CustomerPurchaseOrderItem.LineTotal
HAVING SUM(CustomerPurchaseOrderItem.Quantity*CustomerPurchaseOrderItem.LineTotal) > (SELECT AVG(XX.Quantity*XX.LineTotal)
FROM CustomerPurchaseOrderItem XX where XX.PurchaseOrderID=CustomerPurchaseOrderItem.PurchaseOrderID
GROUP BY XX.PurchaseOrderID)

jtsroberts
Starting Member

2 Posts

Posted - 2008-10-08 : 09:22:48

Thanks for your reply, I tried this solution, but came up with the following.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 09:56:16

thie below query should return only a single value. it seems like you're getting more than 1 value. Can you try putting this in join if its returning more than 1 value.

SELECT AVG(XX.Quantity*XX.LineTotal)
FROM CustomerPurchaseOrderItem XX where XX.PurchaseOrderID=CustomerPurchaseOrderItem.PurchaseOrderID
GROUP BY XX.PurchaseOrderID)

doco
Yak Posting Veteran

77 Posts

Posted - 2008-10-08 : 15:52:54

You could start by using the embedding your code so it may be read.


SELECT Customer.CustomerID                 ,
      Person.FirstName                     ,
      Person.LastName                      ,
      Person.Email                         ,
      CustomerPurchaseOrder.PurchaseOrderID,
      SUM( CustomerPurchaseOrderItem.Quantity *
      CustomerPurchaseOrderItem.LineTotal ) AS 'Order Total'
FROM  CustomerPurchaseOrderItem
      JOIN Part
      ON    Part.PartID = CustomerPurchaseOrderItem.PartID
      JOIN CustomerPurchaseOrder
      ON    CustomerPurchaseOrder.PurchaseOrderID =
            CustomerPurchaseOrderItem.PurchaseOrderID
      JOIN Customer
      ON    Customer.CustomerID = CustomerPurchaseOrder.CustomerID
      JOIN Person
      ON    Person.PersonID = Customer.CustomerID
GROUP BY CustomerPurchaseOrder.PurchaseOrderID,
      Customer.CustomerID                     ,
      Person.FirstName                        ,
      Person.LastName                         ,
      Person.Email                            ,
      CustomerPurchaseOrderItem.Quantity      ,
      CustomerPurchaseOrderItem.LineTotal
HAVING SUM( CustomerPurchaseOrderItem.Quantity *
      CustomerPurchaseOrderItem.LineTotal ) > AVG
      (SELECT SUM( CustomerPurchaseOrderItem.Quantity *
            CustomerPurchaseOrderItem.LineTotal )
      FROM  CustomerPurchaseOrderItem
      GROUP BY CustomerPurchaseOrderItem.PurchaseOrderID
      );

And there is a really handy online formatting tool here.

Education is what you have after you've forgotten everything you learned in school

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 01:37:19

seems like what you want is this

SELECT Customer.CustomerID                 ,
      Person.FirstName                     ,
      Person.LastName                      ,
      Person.Email                         ,
      CustomerPurchaseOrder.PurchaseOrderID,
      SUM( CustomerPurchaseOrderItem.Quantity *
      CustomerPurchaseOrderItem.LineTotal ) AS 'Order Total'
FROM  CustomerPurchaseOrderItem
      JOIN Part
      ON    Part.PartID = CustomerPurchaseOrderItem.PartID
      JOIN CustomerPurchaseOrder
      ON    CustomerPurchaseOrder.PurchaseOrderID =
            CustomerPurchaseOrderItem.PurchaseOrderID
      JOIN Customer
      ON    Customer.CustomerID = CustomerPurchaseOrder.CustomerID
      JOIN Person
      ON    Person.PersonID = Customer.CustomerID
GROUP BY CustomerPurchaseOrder.PurchaseOrderID,
      Customer.CustomerID                     ,
      Person.FirstName                        ,
      Person.LastName                         ,
      Person.Email                            
      CustomerPurchaseOrderItem.Quantity      ,
      CustomerPurchaseOrderItem.LineTotal
HAVING SUM( CustomerPurchaseOrderItem.Quantity *
      CustomerPurchaseOrderItem.LineTotal ) > 
      (SELECT AVG SUM( CustomerPurchaseOrderItem.Quantity *
            CustomerPurchaseOrderItem.LineTotal )
      FROM  CustomerPurchaseOrderItem
      GROUP BY CustomerPurchaseOrderItem.PurchaseOrderID      );

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources