Which Assets Keep Returning with the Same Finding?

The supervisor wants assets with at least two loose-component findings. Botie can already find loose visits and count visits by asset. Now he needs to combine those ideas without counting clear findings toward the repeated-problem threshold.

There are two different filters. The first chooses the visit records we mean. The second decides which resulting asset groups have enough qualifying visits. SQL gives that second filter its own clause: HAVING.

Filter to loose findings, group by asset, then retain counts of at least two.

[
  {
    "asset_id": "P1",
    "loose_visits": 2
  }
]

The first filter asks a question of a visit: was its finding loose? The later filter asks a question of an asset’s group: how many such visits did it have?

Mixing these units creates plausible errors. Counting every visit and then selecting assets with a loose finding can overstate recurrence. The initial report might even match by accident if every visit happens to have the same finding.

Write the row population and the group rule as separate sentences.

Start by showing every asset’s count of loose findings. Inspect that result and only then apply the threshold. This makes it easier to diagnose whether a missing asset was removed by the finding rule or the recurrence rule.

The workshop exposes that intermediate list so you can see counts before the final filter. A numerical result needs its group label beside it.

The group filter should receive counts of the intended population.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT asset_id, COUNT(*) AS loose_visits
FROM inspections WHERE finding = 'loose'
GROUP BY asset_id ORDER BY asset_id;

HAVING COUNT(*) >= 2 retains groups containing enough input rows. The input has already been restricted by WHERE finding = loose, so those rows are all qualifying findings.

The written SELECT clause appears before FROM, but that writing order is not the logical processing explanation. Think of FROM and WHERE establishing rows, grouping establishing collections, and HAVING choosing among those collections. The optimizer can implement an equivalent plan differently.

A group-level count is unavailable as an ordinary row-level WHERE value.

Some databases permit a result alias in HAVING while others apply different name-resolution rules. Repeating COUNT(*) here is short and portable. Later subquery and CTE lessons offer another way to name intermediate results explicitly.

Do not add a non-grouped finding field to HAVING and hope it represents the whole group. Select the relevant visits with WHERE before aggregating, or express a deliberate conditional aggregate.

A bare field from an arbitrary group member does not describe all group members.

Two inspection rows count as two visits under this schema because they have separate visit identities. If the import accidentally duplicated the same real event under two IDs, SQL cannot discover that business error from COUNT alone.

Use source-event keys and integrity checks in the data pipeline. DISTINCT can answer a deliberately different question, but adding it without understanding the grain may hide a broken import rather than repair it.

A correct query still depends on records having the meanings the schema claims.

The changed workshop gives one asset several clear visits and only one loose visit. It must remain excluded. Another asset has two loose visits and should appear.

This example trips a construction that groups and counts all visits before checking the finding. Retest the original case after fixing the order so the rule generalises beyond the changed shift.

The count’s population is as important as its threshold.