SQL join tables with group by and order by

In this page, we are going to discuss the usage of GROUP BY and ORDER BY clause within a join.

Example:

Sample table: agents

Sample table: orders

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:

SELECT agents.agent_code,agents.agent_name,
SUM(orders.advance_amount)
FROM agents,orders
WHERE agents.agent_code=orders.agent_code
GROUP BY agents.agent_code,agents.agent_name
ORDER BY agents.agent_code;

Sample 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

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: Joining tables through referential integrity
Next: Join two tables related by a single column primary key or foriegn key pair