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
+------------+----------------------+--------------------+------------+-----------------+---------+ | 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 | | ....... | 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
Sample table: orders
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
.........
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
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.
PREV : SQL order by with more columns
NEXT : SQL Dual table