In this page we are going to discuss the usage of GROUP BY and ORDER BY clause within a join.
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;
Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.
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.
Did you find this tutorial useful? If so, We’d love for you to share it with some friends JUST CLICK HERE to Tweet the post.
Please do let us know what you thought in the comment section below – We’d love to hear your feedback.