Simplify in sql

Question

I have some task that I should do it with 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!"

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

the sample data that I use is like this

ProdName	Country	Revenue
coco	Argentina	120
bread	Austria	10000
crunch	Austria	13265
Cote de Blaye	Austria	18445
milk	Argentina	254
Tofu	Argentina	279

From this data I want to select only the best product for each country by revenue. In the data there were 21 country. What should I do so I can get the result below

ProductName	Revenue	Country
Tofu	279	Argentina
Côte de Blaye	18445	Austria

The only way in my mind is only filtering the data by each country then get the best product then union all of them like the code I give on top. I wonder if there's another way.

SQL set *set*-based and loops are almost never needed. I would suggest that you provide sample data, desired results, and a clear explanation of what you want. — Gordon Linoff, Mar 10 '21 at 02:13
This seems to be a maximum of summed values per group problem. Why do you need loops? — Shadow, Mar 10 '21 at 02:17
@GordonLinoff Hey Sir, I already add the sample data. Please kindly check it and give some solution you have. Thankyou before for your help. — Julian Salomo, Mar 10 '21 at 05:09
@Shadow yes it's simply maximum of summed values per group. But how to do it? I know we can select the max value for the table, but idk how to maximum for each group. Can you please kindly help me with this one? I already fix my post, and already put some sample data. — Julian Salomo, Mar 10 '21 at 07:32
I doubt if anyone is going to download the rawdatabase, it would be useful if you provided a few representative rows from each table used in the query. ALSO what version of mysql are you on (if version 8 or above then I think row_number() would be useful) — P.Salmon, Mar 10 '21 at 09:13
@P.Salmon Hy Sir. Actually I already give the data sample on the post. I give it right after the link. — Julian Salomo, Mar 10 '21 at 09:22
Your 'sample' is for one table your query contains 4 tables - if you are happy for a solution based on one table I will post shortly. — P.Salmon, Mar 10 '21 at 12:08
Can you have more than one record for a product per country? If not, then you do not even need a sum and it becomes a simple groupwise maximum question. — Shadow, Mar 10 '21 at 12:36
@P.Salmon Solution based on one table already enough for me. Hope you can help me with it. — Julian Salomo, Mar 10 '21 at 13:28
@Shadow I have no problem with the summarize, my only problem is on groupwise maximum. Hope you can help me with it. — Julian Salomo, Mar 10 '21 at 13:29
@P.Salmon I just realized it. Sorry, this is my first time using stackoverflow. thankyou for your help. — Julian Salomo, Mar 10 '21 at 13:35

P.Salmon · Accepted Answer · 2021-03-10T12:26:37.143

0

Using row_number window function or compare to country maxrevenue

DROP TABLe if exists t;
create table t
(ProdName varchar(20),  Country varchar(20),    Revenue int);
insert into t values
('coco' ,'Argentina'    ,120),
('bread'    ,'Austria'     ,10000),
('crunch','Austria'    ,13265),
('Cote de Blaye'    ,'Austria', 18445),
('milk' ,'Argentina'    ,254),
('Tofu' ,'Argentina'    ,279);

select * 
from
(
select prodname,country,revenue, 
         row_number() over(partition by country order by revenue desc) rn
from t
) s
where rn = 1;

or

select * 
from t
join (select t.country,max(t.revenue) maxrevenue from t group by t.country) t1
        on t1.country = t.country and t1.maxrevenue = t.revenue;

+---------------+-----------+---------+----+
| prodname      | country   | revenue | rn |
+---------------+-----------+---------+----+
| Tofu          | Argentina |     279 |  1 |
| Cote de Blaye | Austria   |   18445 |  1 |
+---------------+-----------+---------+----+
2 rows in set (0.001 sec)

edited Mar 10 '21 at 12:26

answered Mar 10 '21 at 12:13

P.Salmon

17,104
2
12
19

If you believe this is a simple group-wise maximum question, then there are really good dupe targets that explain the background as well, not just provide a solution. – Shadow Mar 10 '21 at 12:37
@Shadow eh? true OP has not clearly stated what should happen if duplicates are possible and these solutions don't worry about that possibility..and considering the starting query they are oversimplified.but may draw out a good question from a bad one. – P.Salmon Mar 10 '21 at 12:40
@P.Salmon Hey sir, I just want inform u that my code already work thanks to you. – Julian Salomo Mar 10 '21 at 14:26

Simplify in sql

1 Answers1