SQL join tables with group by and order by
Introduction
This page delves into the practical usage of SQL's GROUP BY and ORDER BY clauses when working with joined tables. These clauses are essential for organizing and summarizing data retrieved from multiple tables in a relational database. By combining these powerful SQL features, users can group data based on specific criteria, calculate aggregate values, and sort the results to gain meaningful insights.
The examples provided in this page demonstrate how to use GROUP BY and ORDER BY in conjunction with table joins to analyze data effectively.
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 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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
...........
To get 'agent_code' and 'agent_name' columns from the table 'agents' and sum of 'advance_amount' column from the table 'orders' after a joining, with following conditions -
1. 'agent_code' of 'agents' and 'orders' must be same,
2. the same combination of 'agent_code' and 'agent_name' of 'agents' table must be within a group,
3. 'agent_code' of 'agents' table should arrange in an order, default is ascending order,
the following SQL statement can be used:
SQL Code:
-- Selecting specific columns: 'agent_code' and 'agent_name' from the 'agents' table, and the sum of 'advance_amount' from the 'orders' table
SELECT agents.agent_code, agents.agent_name, SUM(orders.advance_amount)
-- Performing a Cartesian product (cross join) between the 'agents' and 'orders' tables (implicit join)
FROM agents, orders
-- Defining the join condition in the WHERE clause where 'agents.agent_code' equals 'orders.agent_code'
WHERE agents.agent_code = orders.agent_code
-- Grouping the result set by 'agent_code' and 'agent_name'
GROUP BY agents.agent_code, agents.agent_name
-- Sorting the result set by 'agent_code' in ascending order
ORDER BY agents.agent_code;
Explanation:
- This SQL query retrieves data from two tables: 'agents' and 'orders'.
- It selects specific columns from the 'agents' table: 'agent_code' and 'agent_name', along with the sum of 'advance_amount' from the 'orders' table.
- The query performs a Cartesian product (cross join) between the 'agents' and 'orders' tables, implicitly joining every row from the 'agents' table with every row from the 'orders' table.
- The join condition is specified in the WHERE clause, where 'agents.agent_code' must equal 'orders.agent_code', linking the two tables based on the agent code.
- The result set is then grouped by 'agent_code' and 'agent_name' using the GROUP BY clause. This ensures that the sum of advance amounts is calculated for each unique combination of agent code and name.
- Finally, the result set is sorted by 'agent_code' in ascending order using the ORDER BY clause.
- This query is useful for calculating the total advance amount for each agent, providing insight into the overall performance of each agent in terms of advance payments received.
Output:
AGENT_CODE AGENT_NAME SUM(ORDERS.ADVANCE_AMOUNT) ---------- ---------------------------------------- -------------------------- A001 Subbarao 200 A002 Mukesh 3500 A003 Alex 1000 A004 Ivan 2100 A005 Anderson 3100 A006 McDen 600 A007 Ramasundar 500 A008 Alford 3300 A009 Benjamin 100 A010 Santakumar 3700 A011 Ravi Kumar 900 A012 Lucida 450
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: Joining tables through referential integrity
Next: Join two tables related by a single column primary key or foriegn key pair