SQL GROUP BY Statement | Oracle GROUP BY Clause examples

Published: December 3, 2008    Posted in: Functions, Oracle, SQL TipsTags:

The GROUP BY statement is used in conjunction with the aggregate functions like SUM to provide means of grouping the result by certain table column or columns.

Syntax:

SQL> SELECT columnName, aggregate_function(columnName)
FROM tableName
WHERE
GROUP BY columnName

Examples :

Step 1: Create a table ‘MyTable’ as below.

SQL> create table MyTable (name varchar2(100),address varchar2(500),marks number);

Table created

Step 2: Insert few rows to ‘MyTable’.

SQL> insert into  mytable values ('Chris','Add1',1);
SQL> insert into  mytable values ('John','Add2',2);
SQL> insert into  mytable values ('John','Add2',3);
SQL> insert into  mytable values ('John','Add2',4);
SQL> insert into  mytable values ('Chris','Add1',5);
5 rows inserted

Step 3: Now let us calculate the total marks for Chris and John using GROUP BY Clause.

SQL> select name,SUM(marks) from mytable GROUP BY name;
NAME                                         SUM(MARKS)
John                                                        9
Chris                                                       6

Technorati Tags:
, ,