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:

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

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:

-- Counting the number of occurrences of agent_name, selecting the working_area column,
-- and counting the distinct commission values
-- from the AGENTS table
SELECT COUNT(agent_name), working_area, COUNT(DISTINCT commission)
-- Grouping the results by the working_area column
FROM AGENTS
-- Sorting the results by the count of occurrences of agent_name in ascending order
GROUP BY working_area
ORDER BY 1;

Explanation:

• This SQL code counts the occurrences of agent names, selects the working area, and counts the distinct commission values for each working area in the AGENTS table.
• The SELECT statement uses the COUNT() function to count the occurrences of agent names and the COUNT(DISTINCT commission) function to count the distinct commission values.
• The GROUP BY clause groups the results by the working_area column, aggregating the counts for each working area.
• The ORDER BY clause sorts the results by the count of occurrences of agent names in ascending order.
• The output will consist of rows showing the count of agent names, the working area, and the count of distinct commission values for each working area, sorted by the count of agent names in ascending order.

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

   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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:

-- Selecting the ord_date column, summing up the ord_amount values,
-- and counting the total number of rows
-- from the orders table
SELECT ord_date, SUM(ord_amount), COUNT(*)
-- Filtering groups to retain only those with more than 1 row
FROM orders
-- Grouping the results by the ord_date column
GROUP BY ord_date
-- Sorting the results by ord_date in descending order
ORDER BY ord_date DESC;

Explanation:

• This SQL code calculates the total order amount and counts the number of orders for each order date in the orders table.
• The SELECT statement retrieves data from the ord_date column and calculates the sum of ord_amount values and the count of rows using the SUM() and COUNT() functions, respectively.
• The HAVING clause filters the groups to retain only those with more than 1 row, indicating that there are multiple orders for that particular date.
• The GROUP BY clause groups the results by the ord_date column, aggregating the sum and count for each order date.
• The ORDER BY clause sorts the results by order date in descending order.
• The output will consist of rows showing the order date, the total order amount for that date, and the count of orders for each date, sorted by order date in descending order.

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.

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