Getting the top sales-CodePudding

How to i get the top vendor for each country? I have this code and it shows the connection between the two tables, now I have to get the largest gmv per country.

Here is my working code:

SELECT DISTINCT a.country_name, b.vendor_name, 
       SUM(a.gmv_local) as total_gmw
 from `my-project-67287.order1.order2`a
 join `my-project-67287.vendor1.vendor2` b on a.vendor_id = b.id 
 group by a.country_name, b.vendor_name;

The top 3 should show this:

CodePudding user response：

Assuming you can save that SELECT into a table called vendors, you need to use it as the subquery in the FROM clause. You could use this:

SELECT vendors.country_name, vendors.vendor_name, MAX(vendors.total_gmw)
FROM 
(
    SELECT DISTINCT a.country_name, b.vendor_name, 
           SUM(a.gmv_local) as total_gmw
     from `my-project-67287.order1.order2`a
     join `my-project-67287.vendor1.vendor2` b on a.vendor_id = b.id 
     group by a.country_name, b.vendor_name
) AS vendors
GROUP BY vendors.country_name;

I must mention I have not tested your query, since I do not have your tables, so I assumed it's correct. I only created the vendors table with the required fields and values from your picture. This should print:

CodePudding user response：

SELECT odr.c_name,vdr.v_name,odr.gmv
FROM(
    SELECT *
    FROM(
        SELECT c_name,v_id,gmv
        FROM `order`
        ORDER BY gmv DESC
    )
    GROUP BY c_name
)AS odr
LEFT JOIN vender AS vdr ON vdr.id = odr.v_id
GROUP BY odr.c_name

c_name short for country_name