Group by clause in SQL

Introduction:

   Ø Group by clause is used to group rows used with the SELECT Query.

   Ø Group by clause often used with Aggregate functions such as (SUM(), AVG(), MAX(), MIN(), COUNT()) to group the results.

   Ø Group by must always follow the WHERE clause and precede the ORDER BY clause.

Syntax:

1. SELECT clm_List

   FROM Table_Name

   Where <Condition>

   [GROUP BY clm]

   [ORDER BY clm];

Example:

 Table: Books

Book_Name	Order_Date	Orders
SQL	01-Dec-2000	2
DBMS	05-Sep-2000	3
PL/SQL	06-Oct-2000	4
SQL	09-Sep-2000	3
SQL	06-Oct-2001	2
DBMS	22-Jun-2001	3
PL/SQL	25-Jan-2002	3
SQL	20-jul-2002	4
DBMS	09-Nov-2003	4

Group by Clause:

1. SELECT Book_name,sum(Orders)

   FROM Books

   WHERE To_date(Order_date,’DD-MM-YYYY’) < to_date(‘DD-MM-YYYY’,’10-Jun-2004’)

   GROUP BY Book_Name

   ORDER BY orders desc;

Output:

Book_Name	Sum(Orders)
PL/SQL	7
DBMS	10
SQL	11

Share: