Save SQL Query Results in Variable

Question

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)

Thanks that's exactly what I was looking for, still new to Sql... — Ken
– Ken, Commented Jan 14, 2022 at 3:04

long · Accepted Answer · 2022-01-14 03:36:31Z

3

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)

edited Jan 14, 2022 at 3:36

answered Jan 14, 2022 at 3:03

long

2321 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ken Over a year ago

Thanks but when I try and run this here w3schools.com/SQL/TRYSQL.ASP?FILENAME=TRYSQL_SELECT_ALL it doesn't seem to work?

Ken Over a year ago

Nvm there was just an extra bracket

Collectives™ on Stack Overflow

Save SQL Query Results in Variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related