Close but not quite - Aggregate Functions *solved

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

Close but not quite - Aggregate Functions *solved

Author

Topic

mallorz4
Starting Member

27 Posts

Posted - 2014-01-18 : 15:14:47

I am having a really hard time putting together a few SQL queries. I will only ask about a couple and hopefully the feedback I receive will guide me through the rest.

1. On the code below, I get the following error: "Column 'Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." I've looked this up on Google but I think I need it in laymens terms.

USE MyGuitarShop
SELECT Customers.EmailAddress, COUNT(Orders.CustomerID) AS NumberOfOrders,
	(SELECT SUM(OrderItems.ItemPrice - OrderItems.DiscountAmount * OrderItems.Quantity)
	FROM OrderItems
	WHERE OrderItems.OrderID = Orders.OrderID) AS TotalAmount
FROM Customers LEFT JOIN Orders
	ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.EmailAddress
ORDER BY TotalAmount DESC

2. This one will be hard to explain .. I have to gather a customers email, sum of orders, and sum of orders after a discount is applied. I am having trouble linking the ORDERS table to the CUSTOMER table because they have no fields in common to compare. There is one field in ORDERS that corresponds to ORDERITEMS and one in ORDERITEMS which corresponds with CUSTOMERS, however I cannot seem to find a valid link.

CUSTOMERS TABLE
customerID

ORDERS TABLE
orderID
customerID

ORDERITEMS TABLE
orderID

I realize the second may be vague, but hopefully someone can help. I've been staring at these for hours and getting no where.

Thank you.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-18 : 19:44:25

If the field DiscountAmount is the discount per item, calculating order price with discount should be: Quantity*(ItemPrice-Discount)
If the field DiscountAmount is the discount per order line, calculating order price with discount should be (actually you don't need brackets for this): (Quantity*ItemPrice)-Discount

In the below queries, I assume the field DiscountAmount is the discount per item.

1) move the subselect to the root select

select c.EmailAddress
      ,count(o.CustomerID) as NumberOfOrders
      ,sum(oi.Quantity*(oi.ItemPrice-oi.DiscountAmount)) as TotalAmount
  from Customers as c
       left outer join Orders as o
                    on o.CustomerID=c.CustomerID
       left outer join OrderItems as oi
                    on oi.OrderID=o.OrderID
 group by c.EmailAddress
 order by TotalAmount

2) having linked the tables like above, it's easypeasy

select c.EmailAddress
      ,sum(oi.Quantity*oi.ItemPrice) as TotalAmountWithoutDiscount
      ,sum(oi.Quantity*(oi.ItemPrice-oi.DiscountAmount)) as TotalAmountWithDiscount
  from Customers as c
       left outer join Orders as o
                    on o.CustomerID=c.CustomerID
       left outer join OrderItems as oi
                    on oi.OrderID=o.OrderID
 group by c.EmailAddress
 order by TotalAmount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:15:46

[code]
USE MyGuitarShop
SELECT c.EmailAddress, COUNT(o.CustomerID) AS NumberOfOrders,oi.*
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID
LEFT JOIN (SELECT OrderItems.OrderID,
SUM(OrderItems.ItemPrice - OrderItems.DiscountAmount * OrderItems.Quantity) AS TotalAmount
,sum(oi.Quantity*oi.ItemPrice) as TotalAmountWithoutDiscount
,sum(oi.Quantity*(oi.ItemPrice-oi.DiscountAmount)) as TotalAmountWithDiscount
FROM OrderItems
GROUP BY OrderItems.OrderID)oi
ON oi.OrderID = o.OrderID
GROUP BY c.EmailAddress
ORDER BY TotalAmount DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mallorz4
Starting Member

27 Posts

Posted - 2014-01-19 : 13:40:50

Thank you for your replies. I really cannot thank you enough. Doubling up on the JOINS as you both did was genius. :)

Bitsmed - I went with your approach. Very helpful and informative. I am very appreciative!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources