CODEWITHSUNDEEP
sql-server
jfritts6524
jfritts6524

Reputation: 77

Joining two tables using a Subquery in the FROM Clause

I'm losing my mind on this question I have on my homework. NO I DON'T WANT YOU TO GIVE ME THE ANSWER! I just want to be pushed into the right direction, because I've been struggling with this.

Here is the question.

Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table.

Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.

This is what I have so far:

SELECT EmailAddress, OrderID, SUM(ItemTotal * Quantity) AS OrderTotal
FROM Customers JOIN
    (SELECT OrderID, MAX(ItemTotal * Quantity) AS LargestOrder
     FROM Orders, OrderItems
     WHERE OrderItems.OrderID = Orders.OrderID)
GROUP BY EmailAddress;

The way the tables are set up is:

Customers Table:
CustomerID, EmailAddress, Password, FirstName, LastName, ShippingAddressID, BillingAddressID

OrderItems Table:
ItemID, OrderID, ProductID, ItemPrice, DiscountAmount, DiscountTotal, PriceTotal, ItemTotal, Quantity

Order Table:
OrderID, CustomerID, OrderDate, ShipAmount, TaxAmount, ShipDate, ShipAddressID, CardType, CardNumber,CardExpires, BillingAddressID.

Any help would be greatly appreciated!

Upvotes: 1

Views: 6586

Answers (4)

Bunchhun
Bunchhun

Reputation: 21

Based on the question, first query is the sub query of second query. Here is the query it should be.

SELECT email_address, MAX(order_total) as max_order_total,MIN(order_id) as min_order_id  
FROM(
  SELECT email_address, order_items.order_id, SUM((item_price-discount_amount)*quantity) as order_total 
  FROM customers INNER JOIN orders
    ON customers.customer_id=orders.customer_id
    INNER JOIN order_items
    ON orders.order_id=order_items.order_id
  GROUP BY email_address,order_items.order_id
)v
GROUP BY email_address
ORDER BY max_order_total DESC

Upvotes: 1

kites
kites

Reputation: 1405

SELECT k.email_address, MAX(k.order_total) AS largest_order
FROM (SELECT c.email_address, o.order_id, ((oi.item_price-oi.discount_amount)*oi.quantity) AS order_total
FROM customers c JOIN orders o
ON c.customer_id =o.customer_id
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY c.email_address, o.order_id) as k
GROUP BY k.email_address;

Upvotes: 0

Damian Leszczyński - Vash
Damian Leszczyński - Vash

Reputation: 30865

When the task require to use a select form select a solution might be the Common Table Expression so called CTE allows you to define a result that you can use further on in later quires.

Example: 

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

So to solve your case: 

WITH ORDER_TOTAL (EmailAddress, OrderID, OrderTotal)
AS
(
   SELECT EmailAddress, OrderID, SUM(i.ItemTotal) as OrderTotal
   FROM Orders o INNER JOIN OrderItems i on o.OrderID = i.OrderID
   GROUP BY EmailAddress, OrderID
) SELECT EmailAddress, MAX(OrderTotal) as MaxOrder 
  FROM ORDER_TOTAL 
  GROUP BY EmailAddress;

Upvotes: 0

Josh
Josh

Reputation: 76

You are missing the ON in your join as well as an alias for the select in your join. You also need a source for orders so I added that into your query.

 SELECT c.emailaddress,
       Max(OrderCost) AS LargestOrder
FROM   customers c
       INNER JOIN orders o
               ON c.customerid = o.customerid
       JOIN (SELECT orders.orderid,
                    itemtotal * quantity AS OrderCost
             FROM   orders,
                    orderitems
             WHERE  orderitems.orderid = orders.orderid)largest
         ON largest.orderid = o.orderid
GROUP  BY c.emailaddress

Upvotes: 1

Related Questions