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)
)
/
ITEM | SALE | BILLING_ADDRESS |
---|
Shoes | 120 | Hyderabad |
Bottles | 105 | Secunderabad |
Shoes | 45 | Allahabad |
Bottles | 25 | Nizampet |
Saree | 5000 | Cheerala |
Computer | 210 | Delhi |
Saree | 210 | Kanchi |
Execute this query:
SELECT item, SUM(sale) AS "Total sales"
FROM salesinventory
GROUP BY item;
Output :
ITEM | TOTALSALES |
---|
Shoes | 165 |
Bottles | 130 |
Saree | 5210 |
Computer | 210 |
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)
)
/
NAME | AGE | CLASS | CITY |
---|
Renuka | 16 | 6 | Hyderabad |
Suma | 21 | 6 | Hyderabad |
Sourya | 26 | 7 | Secunderabad |
Vineetha | 21 | 7 | Mumbai |
Sreya | 21 | 8 | Mumbai |
Ramya | 22 | 8 | Mumbai |
Execute this query:
SELECT state, COUNT(*) AS "Number of students"
FROM students
WHERE class > 5
GROUP BY state;
Output :
CITY | NUMBER OF STUDENTS |
---|
Hyderabad | 2 |
Secunderbad | 1 |
Mumbai | 3 |
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_ID | NAME | AGE | DEPARTMENT | SALARY |
---|
1 | Soumya | 21 | Mechanical | 12000 |
2 | Sai charan | 22 | Software | 15000 |
3 | Sai kiran | 23 | Mechanical | 20000 |
4 | Keerthana | 21 | Mechanical | 25000 |
5 | Ajay | 24 | Software | 35000 |
6 | Sweejya | 22 | Software | 45000 |
7 | Nainika | 26 | Software | 14000 |
Execute this query:
SELECT department,
MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;
Output :
Department | Salary |
---|
Mechanical | 12000 |
Software | 14000 |
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 :
Department | Highest Salary |
---|
Mechanical | 25000 |
Software | 45000 |
What Is Oracle
posted on 2019-11-29 01:11:21 - ORACLE Tutorials
ORACLE - Trigger
posted on 2019-11-28 22:14:22 - ORACLE Tutorials
ORACLE - Cursor
posted on 2019-11-28 22:13:54 - ORACLE Tutorials
ChatGPT Prompt Examples
posted on 2023-06-21 22:37:19 - ChatGPT Tutorials
Chat GPT Key Use Cases
posted on 2023-06-21 21:03:17 - ChatGPT Tutorials
Prompt Frameworks
posted on 2023-06-21 19:33:06 - ChatGPT Tutorials