w3resource logo


SQL JOIN GROUP BY AND ORDER BY

SQL join tables with group by and order by

rating Average rating 7 out of 10. Total 98 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

Sql join tables with group by and order by

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.



<<PreviousNext>>