SQL join tables with group by and order by

has average rating 8 out of 10. Total 60 users rated.

<<PreviousNext>>

Description

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 a 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;

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

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.