Programming

SQL Join Tables with Group By and Order By

In this post of easy lesson series, we are going to discuss the usage of GROUP BY and ORDER BY clause within a JOIN clause. First of all you need to know what is a sql join clause.

A SQL join clause combines records from two or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining fields from two tables (or more) by using values common to each.

Sample table : agents

+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | john doe             | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Lawrence             | London             |       0.13 | 075-12458969    |         |
| A008       | Christlin            | New York           |       0.12 | 044-25874365    |         |
| A011       | Kowsik Prasanth      | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Jones Rajakumar      | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Dinesh Kumar         | 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
    200115       2000           1200 08-FEB-08 C00013          A013
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200132       4000           2000 15-AUG-08 C00013          A013
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012
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;

the following sql statement is equivalent version of the above statement (see the JOIN keyword):

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

Above queries may look different but the working principles and output are the same. So both the queries will give the following output when you run them independently.

NOTE: The column name utilized by the ORDER BY clause should be contained in either an aggregate function or the GROUP BY clause.

Output

AGENT_CODE AGENT_NAME                               SUM(ORDERS.ADVANCE_AMOUNT)
---------- ---------------------------------------- --------------------------
A001       Dinesh Kumar                                                    200
A002       Mukesh                                                         3500
A003       Lawrence                                                       1000
A004       Ivan                                                           2100
A005       Anderson                                                       3100
A006       McDen                                                           600
A007       John Doe                                                        500
A008       Christlin                                                      3300
A009       Benjamin                                                        100
A010       Jones Rajakumar                                                3700
A011       Kowsik Prasanth                                                 900
A012       Lucida                                                          450
Previous ArticleNext Article