Home | | Computer Science 12th Std | SQL Aggregate Functions

# SQL Aggregate Functions

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

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

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

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

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

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)]

Tags : Data Manipulation Through SQL , 12th Computer Science : Chapter 15 : Integrating Python with MySql and C++ : Data Manipulation Through SQL
Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail
12th Computer Science : Chapter 15 : Integrating Python with MySql and C++ : Data Manipulation Through SQL : SQL Aggregate Functions | Data Manipulation Through SQL