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

 652 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!