Search code examples
mysqlmysql-workbenchmysql-error-1064

Retrieve sorted data based on one numeric column in MySQL

I have a table like attached image in MySQL database.

MySQL table

I am trying to retrieve sorted data based on SUM(freight) column. For this i have used the below query.

SELECT ShipCountry FROM CountryDetails GROUP BY ShipCountry ORDER BY SUM(freight) ASC

When i run this i am getting result like below.

MySQL result

If i run the below query i am getting result like below. It's fine.

SELECT ShipCountry, ShipCity FROM CountryDetails GROUP BY ShipCountry, ShipCity ORDER BY SUM(Freight), ShipCity ASC

Results

Instead of this i need a result like below. In order by clause SUM(Freight) should consider only ShipCountry. It should not consider both ShipCountry and ShipCity. My Expected result is

Tableau's result

How to achieve this result through MySQL query?

in SQL we can achieve like below query.

Select ShipCountry, ShipCity from Countrydetails group by ShipCountry, ShipCity Order by SUM(SUM(freight)) over(partition by ShipCountry), Shipcity Asc.

We need equivalent query like this in MySQL.

Solution

  • Try this:

    SELECT t1.ShipCountry, t1.ShipCity, t2.countrysum FROM CountryDetails t1 
  join ( select ShipCountry, SUM(freight) countrysum from CountryDetails 
       group by ShipCountry )
  as t2 on t1.ShipCountry = t2.ShipCountry 
GROUP BY ShipCountry, ShipCity 
ORDER BY countrysum ASC ;

    It includes a subquery but should produce a separate line for each country-city pair.