Aggregation Functions

 Aggregation functions in MySQL are used to perform calculations on multiple values and return a single value. These functions are commonly used with the GROUP BY clause. Here are some of the most commonly used aggregation functions in MySQL:

1. COUNT()
Counts the number of rows that match a specified condition.

SELECT COUNT(*)
FROM students;

2. SUM()
Calculates the sum of a set of values.

SELECT SUM(score)
FROM scores;

3. AVG()
Calculates the average value of a set of values.

SELECT AVG(score)
FROM scores;

4. MIN()
Finds the minimum value in a set of values.

SELECT MIN(score)
FROM scores;

5. MAX()
Finds the maximum value in a set of values.

SELECT MAX(score)
FROM scores;

6. GROUP_CONCAT()
Concatenates values from multiple rows into a single string.

SELECT GROUP_CONCAT(course_name)
FROM courses;


7.VARIANCE()
Returns the variance of a numeric column.
SELECT VARIANCE(salary) FROM employees;


8.STDDEV()
Returns the standard deviation of a numeric column.
SELECT STDDEV(salary) FROM employees;


9.BIT_AND()
Returns the bitwise AND of all bits in a column.
SELECT BIT_AND(some_bitwise_column) FROM employees;

10.BIT_OR()
Returns the bitwise OR of all bits in a column.
SELECT BIT_OR(some_bitwise_column) FROM employees;

11.JSON_ARRAYAGG()
Aggregates values as a JSON array.
SELECT JSON_ARRAYAGG(employee_name) FROM employees;

12.JSON_OBJECTAGG()
Aggregates key-value pairs as a JSON object.
SELECT JSON_OBJECTAGG(employee_id, employee_name) FROM employees;

These functions are typically used in combination with the GROUP BY clause to aggregate data across different groups within a table. 

Example:
We'll have two tables: departments and employees.

Departments Table

CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary DECIMAL(10, 2),
    department_id INT,
    join_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Sample Data
Inserting Data into Departments Table

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Marketing');

Inserting Data into Employees Table
INSERT INTO employees (employee_id, employee_name, salary, department_id, join_date) VALUES
(1, 'Alice', 60000, 2, '2020-01-15'),
(2, 'Bob', 50000, 1, '2019-03-20'),
(3, 'Charlie', 70000, 2, '2021-07-30'),
(4, 'David', 55000, 3, '2018-11-12'),
(5, 'Eve', 65000, 2, '2020-05-01');

Aggregation Queries

Total number of employees:
SELECT COUNT(*) AS total_employees FROM employees;

Total salary paid to employees:
SELECT SUM(salary) AS total_salary FROM employees;

Average salary per department:
SELECT department_id, AVG(salary) AS average_salary 
FROM employees 
GROUP BY department_id;

Minimum and maximum salary in the company:
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary 
FROM employees;

Concatenated list of employee names per department:
SELECT department_id, GROUP_CONCAT(employee_name) AS employees 
FROM employees 
GROUP BY department_id;

Variance and standard deviation of salaries:SELECT VARIANCE(salary) AS salary_variance, STDDEV(salary) AS salary_stddev 
FROM employees;

Bitwise AND and OR of employee IDs:SELECT BIT_AND(employee_id) AS bit_and_result, BIT_OR(employee_id) AS bit_or_result 
FROM employees;

Aggregated employee names as JSON array per department:
SELECT department_id, JSON_ARRAYAGG(employee_name) AS employee_names 
FROM employees 
GROUP BY department_id;

Aggregated key-value pairs of employee IDs and names as JSON object:
SELECT JSON_OBJECTAGG(employee_id, employee_name) AS employee_id_name_map 
FROM employees;

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more