Search code examples
sqliteandroid-sqlitesystem.data.sqlitesqlite-net

Retrieve sorted data based on one numeric column in SQLITE

I have a table like attached image in SQLITE database.

Sqlite 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.

Sqlite 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

Result

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 result

How to achieve this result through SQLITE 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 Sqlite.

Solution

  • Use a subquery to find the frieght sum for each country, then order using only this country level sum:

    SELECT
    cd1.ShipCountry,
    cd1.ShipCity
FROM Countrydetails cd1
INNER JOIN
(
    SELECT ShipCountry, SUM(freight) AS freight_sum
    FROM Countrydetails
    GROUP BY ShipCountry
) cd2
    ON cd1.ShipCountry = cd2.ShipCountry
ORDER BY
    cd1.freight_sum,
    cd1.ShipCountry,    -- needed in case two countries happen to have the same sum
    cd1.ShipCity

    We could add another sort level for the frieght of each city, to order within a given country. But your expected output does not imply that you want this.