SQL · Theory

AVG()()

Theory

188/439

AVG()()

AVG(marks) = SUM/COUNT of non-NULL marks. Absentees (NULL) don’t pull the average down. If they should count as 0, AVG(IFNULL(marks,0)).

ROUND(AVG(marks), 2) for two decimals.

Trap — averaging in PHP after SELECT * of 1 lakh rows.

On the example next to this theory — AVG()() — aggregate functions collapse many rows into one summary row.

Exam tip

AVG(marks) ignores NULL. AVG(IFNULL(marks,0)) includes as 0.

AVG()() — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

AVG()() — aggregate functions collapse many rows into one summary row.

Short notes

DefAVG is mean of non-NULL values.
RuleIFNULL if missing should count as 0.
TrapNULL absentees silently excluded.

SQL · Theory

AVG()()

← All stacks

Theory

188/439

AVG()()

AVG(marks) = SUM/COUNT of non-NULL marks. Absentees (NULL) don’t pull the average down. If they should count as 0, AVG(IFNULL(marks,0)).

ROUND(AVG(marks), 2) for two decimals.

Trap — averaging in PHP after SELECT * of 1 lakh rows.

On the example next to this theory — AVG()() — aggregate functions collapse many rows into one summary row.

Exam tip

AVG(marks) ignores NULL. AVG(IFNULL(marks,0)) includes as 0.

AVG()() — sample query

CREATE TABLE scores (id INTEGER PRIMARY KEY, marks INTEGER);
INSERT INTO scores VALUES (1, 80), (2, 90), (3, 70);
SELECT COUNT(*) AS n, AVG(marks) AS avg_marks, MAX(marks) AS top
FROM scores;

AVG()() — aggregate functions collapse many rows into one summary row.

Short notes

DefAVG is mean of non-NULL values.
RuleIFNULL if missing should count as 0.
TrapNULL absentees silently excluded.

AVG()()

AVG()() — sample query

Short notes

Questions

AVG()()

AVG()() — sample query

Short notes

Questions