I'm a SQL beginner, practicing through various sources. I have a table called marketing_data containing product sales information, country and other variables where I'm trying to get an output for the most popular product per country, based on sales. I don't know where to begin with my syntax.

This is how the data looks in the table

I've previously run this code to see total sales for each product per country:

SELECT Country,
    SUM(Liquids) AS TotalLiquids,
    SUM(Veg) AS TotalVeg,
    SUM(NonVeg) AS TotalNonVeg,
    SUM(Fish) AS TotalFish,
    SUM(Chocolates) AS TotalChocolates,
    SUM(Commodities) AS TotalCommodities
FROM marketing_data
GROUP BY COUNTRY;

This gave me a useful table, but I'd like to simply see which product has the highest sales for each country, so The output I'm trying to get would hopefully look something like this:

CodePudding user response：

In most DBMS, you can use the query you've shown as subquery.

And then use GREATEST with CASE WHEN to create the expected outcome.

So you can do following:

SELECT country, 
CASE GREATEST(Liquids, Veg, NonVeg, Fish, Chocolates, Commodities)
          WHEN Liquids    THEN 'Liquids'
          WHEN Veg        THEN 'Veg'
          WHEN NonVeg     THEN 'NonVeg'
          WHEN Fish       THEN 'Fish'
          WHEN Chocolates THEN 'Chocolates'
          ELSE 'Commodities'
          END AS MostPopularProduct
FROM 
(SELECT Country,
    SUM(Liquids) AS Liquids,
    SUM(Veg) AS Veg,
    SUM(NonVeg) AS NonVeg,
    SUM(Fish) AS Fish,
    SUM(Chocolates) AS Chocolates,
    SUM(Commodities) AS Commodities
FROM marketing_data
GROUP BY COUNTRY) sub;

But attention! If the values you want to sum can be NULL, you need to replace null values by another value (very likely, you want them to be zero), otherwise the whole sum will be null, too! That's a typical use case for COALESCE:

SELECT country, 
CASE GREATEST(Liquids, Veg, NonVeg, Fish, Chocolates, Commodities)
          WHEN Liquids    THEN 'Liquids'
          WHEN Veg        THEN 'Veg'
          WHEN NonVeg     THEN 'NonVeg'
          WHEN Fish       THEN 'Fish'
          WHEN Chocolates THEN 'Chocolates'
          ELSE 'Commodities'
          END AS MostPopularProduct
FROM 
(SELECT Country,
    SUM(COALESCE(Liquids,0)) AS Liquids,
    SUM(COALESCE(Veg,0)) AS Veg,
    SUM(COALESCE(NonVeg,0)) AS NonVeg,
    SUM(COALESCE(Fish,0)) AS Fish,
    SUM(COALESCE(Chocolates,0)) AS Chocolates,
    SUM(COALESCE(Commodities,0)) AS Commodities
FROM marketing_data
GROUP BY COUNTRY) sub;

This query will do on Oracle DB's, MySQL DB's, Maria DB's, Postgres DB's and SQLServer 2022 DB's.

If your DBMS is none of them, you can also use this concept, but you likely have to replace GREATEST by a similar function that they provide.