SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL Aggregate Operators
2022-07-24 01:06:34

SQL Aggregate Operators

In SQL, the aggregate operators perform a calculation on multiple rows or multiple values of a single column and give the output a single value.

Here, multiple rows of data are grouped and form a single data.

Types of Aggregate Operators

In SQL, there are five types of aggregate functions/operators to perform the operations on certain data.

  1. sum()
  2. avg()
  3. max()
  4. min()
  5. count()

In SQL, The aggregate functions ignore the NULL values when it performs the calculations on multiple rows, except for the count() operator.

In this concept, we can also use the aggregate operators with the group by ang having classes to combine the data of the select statement.

Sum() Operators

The sum() function gives the total sum of the selected columns of a table

This operator mainly works on the numeric columns only.

Syntax:

sum()

Or

sum([all | distincts]expression)

Example: Let’s take an employee table and perform the sum() operation on it

IDNAMEAGESALARY
11Jack3010000
12John3220000
13Jill3530000
14Rose3740000
15James4050000
13Jill3340000
16Alex3370000

Query: Select sum(salary) from employee;

Output:

SUM(SALARY)
260000

Query: Select sum(eid) from employee;

Output:

SUM(ID)
94

Query: Select sum(age) from employee;

Output:

SUM(AGE)
240

Using group by:

  • Select sum(salary), age from employee group by age;
SUM(SALARY)AGE
10000
20000
30000
40000
50000
110000		30
32
35
37
40
33
  • Select sum(salary), id from employee group by id;
SUM(SALARY)AGE
10000
70000
40000
20000
50000
70000		11
13
14
12
15
16
  • Select sum(salary), name from employee group by name;
SUM(SALARY)NAME
50000
20000
70000
40000
70000
10000		James
John
Jill
Rose
Alex
Jack

Using having clause:

  • Select sum(salary), idfrom employee1 group by id having sum(salary)>40000;
SUM(SALARY)ID
70000
50000
80000		13
15
16
  • Select sum(age) from employee group by id having sum(id)>12;
SUM(ID)NAME
15
26
14
16		James
jill
rose
Alex

Avg() Operators

The avg() function gives the total average of the selected columns of a table

This operator mainly works on the numeric columns, and it returns the average of all non-null values

Syntax:

avg()

Or

avg([all | distincts]expression)

Example: Let’s take an employee table and perform the avg() operation on it

IDNAMEAGESALARY
11Jack3010000
12John3220000
13Jill3530000
14Rose3740000
15James4050000
13Jill3340000
16Alex3370000

Query: Select avg(salary) from employee;

Output:

AVG(SALARY)
37142.8571

Query: Select avg(id) from employee;

Output:

AVG(ID)
13.4285714

Query: Select avg(age) from employee;

Output:

AVG(AGE)
34.2857143

Using group by:

  • Select avg(salary), age from employee group by age;
AVG(SALARY)AGE
10000
20000
30000
40000
50000
550000		30
32
35
37
40
33
  • Select avg(salary), id from employee group by id;
AVG(SALARY)AGE
10000
35000
40000
20000
50000
70000		11
13
14
12
15
16
  • Select avg(salary), name from employee group by name;
SUM(SALARY)NAME
50000
20000
35000
40000
70000
10000		James
John
Jill
Rose
Alex
Jack

Using having clause:

  • Select avg(salary), id from employee1 group by id  having avg(salary)>40000;
AVG(SALARY)ID
50000
70000		15
16
  • Select avg(id),name from employee group by name having avg(id)>12;
AVG(ID)NAME
15
13
14
16		James
Jill
Rose
Alex

Max() Operators

The max() function gives the highest or maximum value of the selected columns of a table, and it is a set of all non-null values.

Syntax:

max()

Or

max([all | distincts]expression)

Example:

Let’s take an employee table and perform the max() operation on it

IDNAMEAGESALARY
11Jack3010000
12John3220000
13Jill3530000
14Rose3740000
15James4050000
13Jill3340000
16Alex3370000

Query: Select max(salary) from employee;

Output:

MAX(SALARY)
70000

Query: Select  max(id) from employee;

Output:

MAX(ID)
16

Query: Select max(age) from employee;

Output:

MAX(AGE)
40

Using group by:

  • Select max(salary), age from employee group by age;
MAX(SALARY)AGE
10000
20000
30000
40000
50000
70000		30
32
35
37
40
33
  • Select max(salary), id from employee group by id;
MAX(SALARY)AGE
10000
40000
40000
20000
50000
70000		11
13
14
12
15
16
  • Select max(salary), name from employee group by name;
MAX(SALARY)NAME
50000
20000
40000
40000
70000
10000		James
John
Jill
Rose
Alex
Jack

Using having clause:

  • select max(salary), id from employee1 group by id having max(salary)>40000;
MAX(SALARY)ID
50000
70000		15
16
  • Select max(id), name from employee group by name having max(id)>12;
AVG(ID)NAME
15
13
14
16		James
Jill
Rose
Alex

Min() Operators

The min() function gives the lowest or minimum value of the selected columns of a table, and it is a set of all non-null values.

Syntax:

min()

Or

max([all | distincts]expression)

Example:

Let’s take an employee table and perform the min() operation on it

IDNAMEAGESALARY
11Jack3010000
12John3220000
13Jill3530000
14Rose3740000
15James4050000
13Jill3340000
16Alex3370000

Query: Select max(salary) from employee;

Output:

MIN(SALARY)
10000

Query: Select max(id) from employee;

Output:

MIN(ID)
11

Query: Select max(age) from employee;

Output:

MIN(AGE)
30

Using group by:

  • Select min(salary), age from employee group by age;
MAX(SALARY)AGE
10000
20000
30000
40000
50000
70000		30
32
35
37
40
33
  • Select min(salary), id from employee group by id;
MAX(SALARY)AGE
10000
40000
40000
20000
50000
70000		11
13
14
12
15
16
  • Select min(salary), name from employee group by name;
MIN(SALARY)NAME
50000
20000
30000
40000
70000
10000		James
John
Jill
Rose
Alex
Jack

Using having clause:

  • select min(salary), id from employee1 group by id  having min(salary)>40000;
MIN(SALARY)ID
50000
70000		15
16
  • Select max(id), name from employee group by name having min(id)>12;
AVG(ID)NAME
15
13
14
16		James
Jill
Rose
Alex

Count() Operators

The count() function gives the number of rows in the table. It is a set  of all non-null values

Syntax:

count()

Or

count([all |distincts]expression)

Example:

Let’s take an employee table and perform the count() operation on it

IDNAMEAGESALARY
11Jack3010000
12John3220000
13Jill3530000
14Rose3740000
15James4050000
13Jill3340000
16Alex3370000

Query: Select count(salary) from employee;

Output:

COUNT(SALARY)
7

Query: Select count(id) from employee;

Output:

COUNT(ID)
7

Query: Select count(age) from employee;

Output:

COUNT(AGE)
7

Using group by:

  • Select max(salary), age from employee group by age;
MAX(SALARY)AGE
1
1
1
1
1
2		30
32
35
37
40
33
  • Select max(salary), id from employee group by id;
MAX(SALARY)AGE
1
1
1
1
1
1		11
13
14
12
15
16
  • Select max(salary), name from employee group by name;
MAX(SALARY)NAME
1
1
1
1
1
1		James
John
Jill
Rose
Alex
Jack

Using having clause:

  1. select count(salary), id from employee1 group by id having count(salary)>40000;
    no rows selected
  2. Select max(id), name from employee group by name having max(id)>12;
    no rows selected