SQL ordering output by column number with group by

Last update on August 19 2022 21:50:45 (UTC/GMT +8 hours)

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.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

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

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