Skip to content
Advertisement

Grouping columns to find most popular product for each country

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 This is how the data looks in the table

I’ve previously run this code to see total sales for each product per 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:

Country Most popular product
Sp Liquids
IND NonVeg

Advertisement

Answer

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:

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:

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement