Umapathy S Umapathy S - 1 year ago 66
MySQL Question

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download