Aggregate: COUNT, AVG, MAX, MIN

Aggregare functions

CREATE TABLE person (
    name TEXT,
    subject TEXT,
    grade INTEGER
);

INSERT INTO person (name, subject, grade) VALUES
    ('Jane',  'Chemistry', 62),
    ('Jane',  'Physics', 70),
    ('Jane',  'Math', 23),
    ('Joe',   'Chemistry', 45),
    ('Joe',   'Physics', 85),
    ('Joe',   'Math', 77),
    ('Peter', 'Chemistry', 35),
    ('Peter', 'Physics', 63),
    ('Mary',  'Chemistry', 95),
    ('Dean', 'Physics', 100),
    ('Dana', 'Math', 97),
    ('George', 'Math', 97),
    ('George', 'Chemistry', 45);


SELECT * FROM person;

SELECT COUNT(*) FROM person;
SELECT 'Total', COUNT(*) FROM person;
SELECT 'Physycs', COUNT(*) FROM person WHERE subject == 'Physics';
SELECT 'Failed', COUNT(*) FROM person WHERE grade < 60;

SELECT 'Maximum all', MAX(grade) FROM person;
SELECT 'Maximum Chemistry', MAX(grade) FROM person WHERE subject == 'Chemistry';

SELECT 'Average all', AVG(grade) FROM person;
SELECT 'Average Chemistry', AVG(grade) FROM person WHERE subject == 'Chemistry';
SELECT 'Average Physics', AVG(grade) FROM person WHERE subject == 'Physics';

$ cat examples/grades.sql  examples/aggregate.sql | sqlite3

Jane|Chemistry|62
Jane|Physics|70
Jane|Math|23
Joe|Chemistry|45
Joe|Physics|85
Joe|Math|77
Peter|Chemistry|35
Peter|Physics|63
Mary|Chemistry|95
Dean|Physics|100
Dana|Math|97
George|Math|97
George|Chemistry|45
13
Total|13
Physycs|4
Failed|4
Maximum all|100
Maximum Chemistry|95
Average all|68.769230769230774
Average Chemistry|56.4
Average Physics|79.5