ORACLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

GROUP BY



Oracle - Group By


In Oracle,GROUP BY clause is used in a SELECT statement to group rows into a set of summary rows by values of columns or expressions.
The GROUP BY clause returns one row per group. The GROUP BY clause is often used with aggregate functions such as AVG() , COUNT() , MAX() , MIN() and SUM() .


Syntax :
SELECT expression1, expression2, ... expression_n,   
aggregate_function (aggregate_expression)  
FROM tables  
WHERE conditions  
GROUP BY expression1, expression2, ... expression_n;  

Parameters :

  • expression1, expression2, ... expression_n: It specifies the expressions that are not encapsulated within aggregate function. These expressions must be included in GROUP BY clause.

  • aggregate_function: It specifies the aggregate functions i.e. SUM, COUNT, MIN, MAX or AVG functions.

  • aggregate_expression: It specifies the column or expression on that the aggregate function is based on.

  • tables: It specifies the table from where you want to retrieve records.

  • conditions: It specifies the conditions that must be fulfilled for the record to be selected.


    Oracle GROUP BY Example: (with SUM function)


    Let's take a table "salesinventory"

    salesinventory table:
    CREATE TABLE  "SALESINVENTORY"   
       (    "ITEM" VARCHAR2(4000),   
        "SALE" NUMBER,   
        "BILLING_ADDRESS" VARCHAR2(4000)  
       )  
    /  
    

    ITEMSALEBILLING_ADDRESS
    Shoes120Hyderabad
    Bottles105Secunderabad
    Shoes45Allahabad
    Bottles25Nizampet
    Saree5000Cheerala
    Computer210Delhi
    Saree210Kanchi

    Execute this query:
    SELECT item, SUM(sale) AS "Total sales"  
    FROM salesinventory
    GROUP BY item;  
    

    Output :

    ITEMTOTALSALES
    Shoes165
    Bottles130
    Saree5210
    Computer210

    The above example will show the total sales of every individual item.


    Oracle GROUP BY Example: (with COUNT function)


    Let's take a table "students"
    Here we are creating a table named customers. This table doesn't have any primary key.

    Students table:
    CREATE TABLE  "STUDENTS"   
       (    "NAME" VARCHAR2(4000),   
        "AGE" NUMBER,   
        "CLASS" NUMBER,   
        "CITY" VARCHAR2(4000)  
       )  
    /  
    

    NAMEAGECLASSCITY
    Renuka166Hyderabad
    Suma216Hyderabad
    Sourya267Secunderabad
    Vineetha217Mumbai
    Sreya218Mumbai
    Ramya228Mumbai

    Execute this query:

    SELECT state, COUNT(*) AS "Number of students"  
    FROM students  
    WHERE class > 5  
    GROUP BY state;  
    

    Output :

    CITYNUMBER OF STUDENTS
    Hyderabad2
    Secunderbad1
    Mumbai3

    Oracle GROUP BY Example: (with MIN function)


    Let's take a table "employees"

    Employees table:
    CREATE TABLE  "EMPLOYEES"   
       (    "EMP_ID" NUMBER,   
        "NAME" VARCHAR2(4000),   
        "AGE" NUMBER,   
        "DEPARTMENT" VARCHAR2(4000),   
        "SALARY" NUMBER  
       )  
    /  
    

    EMP_IDNAMEAGEDEPARTMENTSALARY
    1Soumya21Mechanical12000
    2Sai charan22Software15000
    3Sai kiran23Mechanical20000
    4Keerthana21Mechanical25000
    5Ajay24Software35000
    6Sweejya22Software45000
    7Nainika26Software14000

    Execute this query:
    SELECT department,   
    MIN(salary) AS "Lowest salary"  
    FROM employees  
    GROUP BY department;  
    

    Output :
    DepartmentSalary
    Mechanical12000
    Software14000

    Oracle GROUP BY Example: (with MAX function)


    In this example, we are using "employees" table that is given above.

    Execute this query:
    SELECT department,  
    MAX(salary) AS "Highest salary"  
    FROM employees  
    GROUP BY department;  
    

    Output :

    DepartmentHighest Salary
    Mechanical25000
    Software45000


    What Is Oracle

    What Is Oracle

    posted on 2019-11-29 01:11:21 - ORACLE Tutorials


    TRIGGER

    ORACLE - Trigger

    posted on 2019-11-28 22:14:22 - ORACLE Tutorials


    CURSOR

    ORACLE - Cursor

    posted on 2019-11-28 22:13:54 - ORACLE Tutorials


    MySQL TRIGGERS

    MySQL - Triggers

    posted on 2019-11-29 21:44:07 - mysql Tutorials


    Grant_ Revoke Privilege

    MySQL - Grant_ Revoke Privilege

    posted on 2019-11-26 23:15:04 - mysql Tutorials


    MySQL Vs SQL

    MySQL Vs SQL

    posted on 2019-11-25 05:02:26 - mysql Tutorials