print(*result,sep="\n") OUTPUT ( , 'Akshay', 'B') ( , 'VARUN', 'B') Aggregate Functions . These functions are used to do operations from the values of the column and a single value is returned. 12th Computer Chapter - - Data Manipulation Through SQL • 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 if there were no matching rows. Example . .
- import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT COUNT(*) FROM student ") result = cursor.fetchall() print(result) Output: [( ,)] Example : In this example we are going to count the number of records(rows) EXAMPLE . . - import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT COUNT(AVERAGE) FROM student ") result = cursor.fetchall() print(result Output: [( ,)] Example : In this example we are going to count the number of records by specifying a column 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 Note 12th Computer Chapter - - .
.2AVG(): 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 [( .65714285714286,)] NULL values are ignored.
Note . . 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 [( . ,)] NULL values are ignored. Note 12th Computer Chapter - - Data Manipulation Through SQL .
. 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")