SQLite Grouping, Aggregates and HAVING

Aggregate functions summarise several rows. COUNT(*) counts rows, while COUNT(column) counts non-NULL values of that column. AVG, SUM, MIN and MAX ignore NULL inputs in the ordinary cases considered here. A missing measurement does not contribute a zero to the average. For readings 10, 20 and NULL, the known-value average is 15, not 10.

GROUP BY creates a result group for each grouping value or combination. For each site, select its grouping key and the intended aggregates. Avoid selecting an arbitrary non-grouped descriptive column and assuming every database will choose the same value. SQLite permits some bare-column behaviour that can obscure the question; portable teaching queries make the grouping intent explicit.

WHERE restricts input rows before grouping. HAVING restricts groups after aggregation. A request for sites with an average known reading of at least 20 can use HAVING AVG(Reading)>=20. A request for the average of readings individually at least 20 first uses WHERE Reading>=20, which changes the population being averaged. These are different questions even when a small dataset happens to produce a similar answer.

The fixture gives Meadow readings 10, 20 and NULL, Ridge reading 30, and Fen no sample. A LEFT JOIN preserves Fen. COUNT(*) for Fen is 1 because the join creates one NULL-extended row, but COUNT(m.SampleID) is 0 because no sample identifier exists. For Meadow, the sample count is 3 while the count of known readings is 2. State which quantity the task asks for.

With no matching numeric inputs, SUM and AVG normally return NULL, while COUNT returns zero. Do not silently replace an unknown average with zero unless the scenario explicitly defines that policy. A valid zero measurement is evidence of a value, whereas the absence of measurements is different information. At the Python boundary, NULL appears as None.

The example checks each site’s sample count, known-reading count, total, mean, minimum and maximum. It then applies a HAVING threshold to the ungrouped sample table by SiteID. Validate expected groups independently from the code and test an all-missing group, an absent group, a threshold equality and a group just below the threshold. ORDER BY fixes result order for comparison and presentation.

import sqlite3

connection = sqlite3.connect(':memory:')
try:
    connection.execute('CREATE TABLE Site (SiteID INTEGER PRIMARY KEY, Name TEXT)')
    connection.execute('CREATE TABLE Sample (SampleID INTEGER PRIMARY KEY, SiteID INTEGER, Reading REAL)')
    connection.executemany('INSERT INTO Site VALUES (?, ?)', [(1, 'Meadow'), (2, 'Ridge'), (3, 'Fen')])
    connection.executemany('INSERT INTO Sample VALUES (?, ?, ?)', [(10, 1, 10), (11, 1, 20), (12, 1, None), (13, 2, 30)])
    sql = '''SELECT s.Name, COUNT(*), COUNT(m.SampleID), COUNT(m.Reading),
                    SUM(m.Reading), AVG(m.Reading), MIN(m.Reading), MAX(m.Reading)
             FROM Site s LEFT JOIN Sample m ON s.SiteID=m.SiteID
             GROUP BY s.SiteID, s.Name ORDER BY s.SiteID'''
    groups = connection.execute(sql).fetchall()
    print(groups)
    print(connection.execute('SELECT SiteID, AVG(Reading) FROM Sample GROUP BY SiteID HAVING AVG(Reading)>=? ORDER BY SiteID', (20,)).fetchall())
    print(connection.execute('SELECT COUNT(*), SUM(Reading), AVG(Reading) FROM Sample WHERE SiteID=?', (99,)).fetchone())
finally:
    connection.close()

Attempt the task before opening its worked response. Record each change of state, explain the rule used, and check the result against the original requirements. Code examples labelled Python 3 use that language’s conventions; pseudocode states its own assumptions.