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)
x
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)
Advertisement
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)