Save SQL Query Results in Variable

I have the following SQL Query, but it is very repetitive and so I am wondering if this can be cleaned up by storing the results of a SQL Query in a variable.

What I have is (which works, can be tested here)

SELECT ProductName FROM (
  SELECT ProductName, SUM(Quantity) AS ProductQuantity FROM
    (SELECT CustomerID FROM Customers AS C
    WHERE Country = 'Germany') AS CG
  INNER JOIN Orders AS O
  ON CG.CustomerID = O.CustomerID
  INNER JOIN OrderDetails AS OD
  ON O.OrderID = OD.OrderID
  INNER JOIN Products as P
  ON OD.ProductID = P.ProductID
  GROUP BY ProductName)
WHERE ProductQuantity = (
SELECT MAX(ProductQuantity) FROM (
  SELECT ProductName, SUM(Quantity) AS ProductQuantity FROM
  (SELECT CustomerID FROM Customers AS C
  WHERE Country = 'Germany') AS CG
  INNER JOIN Orders AS O
  ON CG.CustomerID = O.CustomerID
  INNER JOIN OrderDetails AS OD
  ON O.OrderID = OD.OrderID
  INNER JOIN Products as P
  ON OD.ProductID = P.ProductID
  GROUP BY ProductName))

And as you could tell the following code is repeated twice, is there any way I can do this without repeating this code:

(SELECT ProductName, SUM(Quantity) AS ProductQuantity FROM
  (SELECT CustomerID FROM Customers AS C
  WHERE Country = 'Germany') AS CG
  INNER JOIN Orders AS O
  ON CG.CustomerID = O.CustomerID
  INNER JOIN OrderDetails AS OD
  ON O.OrderID = OD.OrderID
  INNER JOIN Products as P
  ON OD.ProductID = P.ProductID
  GROUP BY ProductName)

Answer

You can use CTE to avoid looping, maybe like this:

with cte as (
      SELECT ProductName, SUM(Quantity) AS ProductQuantity FROM
      (SELECT CustomerID FROM Customers AS C
      WHERE Country = 'Germany') AS CG
      INNER JOIN Orders AS O
      ON CG.CustomerID = O.CustomerID
      INNER JOIN OrderDetails AS OD
      ON O.OrderID = OD.OrderID
      INNER JOIN Products as P
      ON OD.ProductID = P.ProductID
      GROUP BY ProductName
    )
SELECT ProductName 
FROM cte
where ProductQuantity = (SELECT MAX(ProductQuantity) FROM cte)

Advertisement

Answer