I have these two queries. I have no idea how to combine them together to make a derived table. I'm suppose to use the second query as the main query and use the first query in the FROM clause of the main query.
SELECT EmailAddress, Orders.OrderID, SUM(ItemPrice * Quantity) AS OrderTotal
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress, Orders.OrderID;
SELECT EmailAddress, MAX(ItemPrice) as LargestOrder
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress
Are you just wanting the results to be in the same table? Just do this:
SELECT FirstTable.*, SecondTable.LargestOrder
FROM (
SELECT EmailAddress, Orders.OrderID, SUM(ItemPrice * Quantity) AS OrderTotal
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress, Orders.OrderID) as FirstTable
JOIN (
SELECT EmailAddress, MAX(ItemPrice) as LargestOrder
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress) as OtherTable ON FirstTable.EmailAddress = OtherTable.EmailAddress
You could Use "Window Functions" instead so something like this
SELECT DISTINCT
EmailAddress,
Orders.OrderID,
SUM(ItemPrice * Quantity) OVER (PARTITION BY EmailAddress, Orders.OrderID) AS OrderTotal,
MAX(ItemPrice) OVER (PARTITION BY EmailAddress) AS LargestOrder
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
Otherwise, you can combine your two queries into each their own common table expression and join together on those while selecting out.
WITH Total AS (
SELECT EmailAddress, Orders.OrderID, SUM(ItemPrice * Quantity) AS OrderTotal
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress, Orders.OrderID
), Largest AS (
SELECT EmailAddress, MAX(ItemPrice) as LargestOrder
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID
GROUP BY EmailAddress
)
SELECT Total.EmailAddress, Total.OrderId, OrderTotal, LargestOrder
FROM Total
INNER JOIN Largest ON Total.EmailAddress = Largest.EmailAddress