sqlsortingunion-all
Achieving Tabular Pivot Type report in SQL
I have the below code which I am trying to get the info of Population of City, County Total for those cities and State Total for all counties in the state. I was able to crack most of it except the State sorting or in genaral State sorting.
ORDER BY State, County, SortOrder, City, Population
Can someone help to check and recommend sorting changes to get the correct output? Let me know if you have any questions.
Solution
This looks like an ideal opportunity to use grouping sets.
You can express all these queries as a single query with the following grouping sets:
select State,
Coalesce(County, 'State Total:') County,
Coalesce(City, case when County is null then '' end, 'County Total:') City,
Sum(Population) Population
from t
group by grouping sets(
(State, County, City),
(State, County),
(State)
)
order by State,
case when County = 'State Total:' then 1 end,
case when City = 'County Total:' then 1 end;
See a demo Fiddle
And output:
- How can i insert/ select into a table and delete copied records from original table?
- Synapse/ ADF - How to Truncate table if dynamic config column is True in pre-copy script
- Fastest way to determine if record exists
- Select only distinct values from two tables on pgadmin without getting all combinations
- Unrecognized column name that I define
- How to merge 2 or more rows and SUM a column in an UPDATE without a primary key?
- SEDE Data Query to find posts that have hidden comments added by me
- What is the most efficient way to count rows in a table in SQLite?
- Renaming foreign-key columns in MySQL
- Fetch dates within the next 5 days from today, from a column of dates
- Preg split multi SQL by semicolon in PHP
- Mysql Pivot View
- Is it good practice to have a column for each user and a row for each user in SQL table?
- How to filter Select option to ALL
- error column cannot be null, trying to upload file into sql
- Function vs. Stored Procedure in SQL Server
- Left side of LIKE expression must evaluate to a varchar (actual: varbinary). What's an alternative to converting from varbinary to varchar?
- How to show the same field value from another entity attributes
- Big Query - Get continuous 7 characters from a long string in Big Query
- How to correctly join two tables that each have Start Date and Stop Date columns?
- PHP XAMPP Config
- SQL select distinct with condition
- To get the previous record which is less than the given date and time
- Create OBJECT with column values as Key in Snowflake
- How to load multiline csv feature into MySQL
- SQL copying data with new foreign keys
- An arithmetic expression with a DATETIME value is invalid while using PreparedStatement with Arithmetic calculation in sql later
- space is not allowed after parameter prefix ':' in SpEL support in Spring Data JPA @Query definitions
- postgres- plsql function to get rows counts for specific columns with null data
- SQLAlchemy - Get query results in same order as IN clause