IT Tutorial

GROUP BY Statement in Oracle SQL | Oracle SQL Tutorials -16

Hi,

I will explain GROUP BY Statement in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

IN and BETWEEN Operator in Oracle SQL | Oracle SQL Tutorials -15

 

 

GROUP BY Statement

GROUP BY statement is very popular in Oracle SQL. You can group specific or all rows which have same values into summary rows.

Aggregate functions (MAX, MIN, SUM, AVG,COUNT) are often used in the Group by statements.

 

 

GROUP BY Syntax

GROUP BY syntax is as follows.

SELECT column1,column2 FROM table_name WHERE condition GROUP BY column1,column2;

SELECT column1,column2 FROM table_name WHERE condition GROUP BY column1,column2
ORDER BY column_name(s);
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
ORDER BY column_name(s);
Group by examples are as follows.
SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40) group by first_name,last_name,salary;  

FIRST_NAME           LAST_NAME                     SALARY
-------------------- ------------------------- ----------
Alexander            Khoo                            4100
Pat                  Fay                             7000
Karen                Colmenares                      3500
Michael              Hartstein                      14000
Guy                  Himuro                          3600
Den                  Raphaely                       12000
Shelli               Baida                           3900
Sigal                Tobias                          3800
Susan                Mavris                          7500

9 rows selected.


SQL> select salary,count(*) from hr.employees where department_id in (20,30,40,50,60,70) group by salary; 

    SALARY   COUNT(*)
---------- ----------
      5200          2
     12000          1
      9000          1
      4000          2
      5000          1
     14000          1
     11000          1
     10000          1
      3900          3
      3400          2
      4600          2

    SALARY   COUNT(*)
---------- ----------
      4500          1
      3800          4
      3500          6
      4100          4
      4400          1
      4800          1
      8900          1
      7500          2
      4200          4
      4300          2
      4900          1

    SALARY   COUNT(*)
---------- ----------
      3600          4
      3700          2
      5800          2
      6800          1
      3200          2
      3100          1
      5100          1
      7000          2
      9200          1

31 rows selected.

SQL>


SQL> select department_id,sum(salary) from hr.employees group by department_id;

DEPARTMENT_ID SUM(SALARY)
------------- -----------
          100       57608
           30       30900
                     8000
           90       46000
           20       21000
           70       11000
          110       22308
           50      201400
           80      338500
           40        7500
           60       33800

DEPARTMENT_ID SUM(SALARY)
------------- -----------
           10        5400

12 rows selected.

SQL>

 

Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

 902 views last month,  2 views today

Exit mobile version