SQL ordering output by column number with group by

In this page, we are going to discuss, how an index number for a column can be used to make the result of a query in descending order based on that column with GROUP BY clause.

Example:

Sample table: agents

To get the number of agents for each group of 'working_area' and number of unique 'commission' for each group of 'working_area' by an arranged order on column number 1 i.e. number of agents for each group of 'working_area' from the mentioned column list from the 'agents' table, the following SQL statement can be used :

SQL Code:

SELECT COUNT(agent_name), working_area,
COUNT(DISTINCT commission)
FROM AGENTS
GROUP BY working_area
ORDER BY 1;

Output:

COUNT(AGENT_NAME) WORKING_AREA                        COUNT(DISTINCTCOMMISSION)
----------------- ----------------------------------- -------------------------
                1 Torento                                                     1
                1 San Jose                                                    1
                1 New York                                                    1
                1 Chennai                                                     1
                1 Hampshair                                                   1
                1 Mumbai                                                      1
                1 Brisban                                                     1
                2 London                                                      2
                3 Bangalore                                                   2

SQL ordering output by column number with group by and having

Sample table: orders

To get 'ord_date', sum of 'ord_amount' for each group of 'ord_date' and number of agents for each group of 'ord_date' from the 'agents' table with the following condition -

1. number of agents for each group of 'ord_date' must be more than 1,

the SQL statement can be used:

SQL Code:

SELECT ord_date, SUM(ord_amount),COUNT(*) 
FROM orders 
HAVING COUNT(*)>1 
GROUP BY ord_date 
ORDER BY ord_date DESC; 

Output:

ORD_DATE  SUM(ORD_AMOUNT)   COUNT(*)
--------- --------------- ----------
16-SEP-08            9000          4
30-JUL-08            6000          2
20-JUL-08            9500          5

See our Model Database

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: SQL order by with more columns
Next: SQL Dual table