How Much Work Belongs to Each Asset?

The inspection total tells Botie how much recorded work the team did. It does not tell the supervisor which asset consumed that time. We need to place each visit with the other visits for the same asset before calculating.

GROUP BY defines those collections. The result changes from one row per visit to one row per asset that appears in the selected log. That last qualification will matter when we later include assets with no visits.

Group by asset_id, sum minutes within each group, then order the resulting assets.

[
  {
    "asset_id": "P1",
    "total_minutes": 45
  },
  {
    "asset_id": "P2",
    "total_minutes": 10
  },
  {
    "asset_id": "P3",
    "total_minutes": 20
  }
]

One row per asset means asset_id is the grouping key. Grouping by finding would answer a different question, even though it uses the same source visits. Multiple grouping columns define a combination, such as one row per asset and finding.

Predict how many groups the visible data should create before running the query. If the output contains more rows than expected, inspect which extra fields were included in GROUP BY.

The grouping key describes the new result grain.

GROUP BY partitions rows according to the selected key values. Aggregate functions then operate separately within each group. COUNT(*) counts the visits in that group; SUM(minutes) combines its known durations.

The select list should describe those group keys and their aggregates. Do not add inspection_id merely because it exists on the source rows. There is no single visit ID for an asset with several visits.

A grouped row contains facts about a group, not an arbitrary representative visit.

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 visits, COUNT(minutes) AS measured
FROM inspections GROUP BY asset_id ORDER BY asset_id;

If the question becomes one subtotal per asset and finding, include both fields in GROUP BY and in the report. Otherwise two result rows can look identical while actually describing different groups.

NULL grouping values form a group rather than disappearing. This grouping rule differs from an equality predicate involving NULL. A missing finding can therefore appear as its own category in a findings summary.

The visible labels should be sufficient to identify which group a result describes.

GROUP BY over inspections can only produce groups from visits that survived the input filter. It cannot invent a P4 group when P4 has no inspection row. An asset-register report that must retain P4 needs a different source arrangement, which the left-join lesson will build.

An empty input produces no grouped rows. This differs from the single summary row of an aggregate query without GROUP BY.

The table chosen in FROM determines which entities are available to group.

A grouping implementation may sort keys internally or use another strategy. ORDER BY asset_id explicitly requests the report’s display order. Do not depend on a grouping plan to provide a stable presentation.

The workshop’s changed input interleaves visits for two assets. Group membership depends on key equality, not on adjacent rows or a change in the visible sequence.

A group can contain records that were far apart in the source list.