Simplify with loop in sql

I have some task that I have to use loop function. I usually use RStudio, but now I should do it with sql. I wonder if there is some loop function "for" like Rstudio ini SQL. Below is the function I currently use.

(SELECT ProductName, sum(Quantity*Price) Revenue, Country    FROM products p      JOIN orderdetails d        ON p.ProductID = d.ProductID        JOIN orders o          ON o.OrderID = d.OrderID          JOIN customers c            ON c.CustomerID = o.CustomerID      WHERE Country =       (        SELECT DISTINCT Country          FROM customers            LIMIT 1      )        GROUP BY ProductName          ORDER BY Revenue DESC            LIMIT 1)  UNION  (SELECT ProductName, sum(Quantity*Price) Revenue, Country    FROM products p      JOIN orderdetails d        ON p.ProductID = d.ProductID        JOIN orders o          ON o.OrderID = d.OrderID          JOIN customers c            ON c.CustomerID = o.CustomerID      WHERE Country =       (        SELECT DISTINCT Country          FROM customers            LIMIT 1,1      )        GROUP BY ProductName          ORDER BY Revenue DESC            LIMIT 1)          UNION  (SELECT ProductName, sum(Quantity*Price) Revenue, Country    FROM products p      JOIN orderdetails d        ON p.ProductID = d.ProductID        JOIN orders o          ON o.OrderID = d.OrderID          JOIN customers c            ON c.CustomerID = o.CustomerID      WHERE Country =       (        SELECT DISTINCT Country          FROM customers            LIMIT 2,1      )        GROUP BY ProductName          ORDER BY Revenue DESC            LIMIT 1)  

My task is "Find best selling products based on revenue for each country!"

I want to use loop function on:

SELECT DISTINCT Country     FROM customers         LIMIT x,1  

I want to make loop on x from 0 to 20. so I can simplify my first code, and then I need to union the result. The result I want is below: | ProductName | Revenue | Country | | ------------- | ------- | --------- | | Tofu | 279 | Argentina | | Côte de Blaye | 18445 | Austria |

You can access the data I use from this link RawDatabase

https://stackoverflow.com/questions/66557365/simplify-with-loop-in-sql March 10, 2021 at 10:03AM