Derived table in SQL Server

Question

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 trying to get max of single order total or max of sum order total made by customer? — Miller, Feb 19 '14 at 18:45
I'm trying to combine the results of the two queries in one table — jaramore, Feb 19 '14 at 18:50

score 2 · Accepted Answer · answered Feb 19 '14 at 18:42

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

score 1 · Answer 2 · answered Feb 19 '14 at 18:46

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

Derived table in SQL Server

2 Answers2

Linked