SQL Aggregate Functions

Aggregate Functions

These functions are used to do operations from the values of the column and a single value is returned.

•  COUNT()         

•  AVG()

•  SUM()

•    MAX()

•  MIN()

COUNT() function

The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. COUNT() returns 0 if there were no matching rows.

Example

Example 1 : In this example we are going to count the number of records(rows)

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT COUNT(*) FROM student ")

result = cursor.fetchall()

print(result)

Output:

[(7,)]

EXAMPLE

Example 2 : In this example we are going to count the number of records by specifying a column

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT COUNT(AVERAGE) FROM student ")

result = cursor.fetchall()

print(result)

Output:

[(7,)]

Note

NULL values are not counted. In case if we had null in one of the records in student table for example in Average field then the output would be 6.

AVG():

The following SQL statement in the python program finds the average mark of all students.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT AVG(AVERAGE) FROM student ")

result = cursor.fetchall()

print(result)

OUTPUT

[(84.65714285714286,)]

Note

NULL values are ignored.

SUM():

The following SQL statement in the python program finds the sum of all average in the Average field of “Student table”.

Example

import sqlite3

connection = sqlite3.connect("Academy.db")

cursor = connection.cursor()

cursor.execute("SELECT SUM(AVERAGE) FROM student ")

result = cursor.fetchall()

print(result)

OUTPUT

[(592.6,)]

MAX() AND MIN() FUNCTIONS

The MAX() function returns the largest value of the selected column.

The MIN() function returns the smallest value of the selected column.

The following example show the highest and least average student’s name.

Example

import sqlite3

connection = sqlite3.connect("Organization.db")

cursor = connection.cursor()

print("Displaying the name of the Highest Average")

cursor.execute("SELECT sname,max(AVERAGE) FROM student ")

result = cursor.fetchall()

print(result)

print("Displaying the name of the Least Average")

cursor.execute("SELECT sname,min(AVERAGE) FROM student ")

result = cursor.fetchall()

print(result)

OUTPUT

Displaying the name of the Highest Average

[('PRIYA', 98.6)]

Displaying the name of the Least Average

[('TARUN', 62.3)]