I have a table like attached image in MySQL database.
I am trying to retrieve sorted data based on SUM(freight) column. For this i have used the below query.
GROUP BY ShipCountry
ORDER BY SUM(freight) ASC
When i run this i am getting result like below.
If i run the below query i am getting result like below. It's fine.
SELECT ShipCountry, ShipCity
GROUP BY ShipCountry, ShipCity
ORDER BY SUM(Freight), ShipCity ASC
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
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.
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.