Retrieve sorted data based on one numeric column in SQL
Umapathy S
I have a table like attached image in SQL server.
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.
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
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 SQL query?
DhruvJoshi
You can try a query like below
select
ShipCountry,
ShipCity
from
(
Select
ShipCountry,
ShipCity,
SUM(Freight) OVER( Partition by ShipCountry order by ShipCountry) NetCountry,
SUM(Freight) OVER( Partition by ShipCountry,ShipCity order by ShipCountry) NetCity
from
CountryDetails
) T
Order by NetCountry asc,ShipCity asc
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
編集
- 前の投稿：Swagger - Formdata with a form-urlEncoded Item
- 次の投稿：Docker + Django、サーバーは実行されていますが、ウェルカムページが表示されていません
関連記事
Related 関連記事
- 1
How to order by one column, but rank based on a different column that is not numeric?
- 2
Retrieve substring of a column name, then apply them to corresponding column data? SQL
- 3
Retrieve multiple status into one row based on multiple conditions SQL 2008
- 4
Oracle - SQL query to bin the data from one table to another based on a column value?
- 5
Oracle - SQL query to bin the data from one table to another based on a column value?
- 6
Show One Column Data as Two Columns in SQL
- 7
Merge data into one column - sql server 2012
- 8
SQLite: How to retrieve data from column in one table using SELECT to insert retrieved data in another table
- 9
SQL - Selecting unique values from one column then filtering based on another
- 10
SQL/VBA - Multiple Columns from one column based on criteria
- 11
How to add a rule in one column based off the data of another
- 12
Join three tables based on one key, putting data into same column
- 13
Sort Data based on one column affecting the entire table VBA
- 14
R: Multiplying One Column to the Rest of the Data Frame Based on Index/Position
- 15
SQL create new column based on the one-to-one or one-to-many relationship between two original columns
- 16
SQL SELECT Data from other table based on column value
- 17
T SQL Group column with accumulating data in one record
- 18
SQL sum data between 2 dates in one column
- 19
In SQL Joins - one to many select one column multiple times based upon context
- 20
Partitioning based on column data?
- 21
Getting formatted and sorted data from SQL
- 22
Align data in one column with another row, based on the last time some condition was true
- 23
Add a new column that maps one character string onto a new character string based on a "Rosetta Stone" data frame?
- 24
Sort data in order based on values in one columns conditional upon another column
- 25
SQL query - limit resulting query rows based on non sorted value
- 26
Replacing NAs witha new factor level in one column based on factor level in another column using data.table
- 27
MYSQL - Retrieve all child IDs in one column, given a parent ID
- 28
Retrieve just one value for each id in column A in excel
- 29
Select data from one table & then rename the columns based on another table in SQL server
コメントを追加