Which Columns Answer the Question?

Botie’s asset register is useful to technicians, but the stores team has a different question: which kinds of equipment are present at each site? They do not need a separate line for every pump.

We can first keep site and kind, then remove repeated pairs. That order matters. Two pumps at the same site give the stores team one category, while pumps at different sites still need separate lines.

Project site and kind before removing complete duplicate rows. Then order the pairs for the report.

[
  {
    "site": "north",
    "kind": "fan"
  },
  {
    "site": "north",
    "kind": "pump"
  },
  {
    "site": "south",
    "kind": "pump"
  }
]

The question asks for a site and an equipment kind. Keeping asset_id as well would make P3 and P4 different result rows, so DISTINCT would preserve both. Dropping site would collapse pumps from different locations into a single category.

Projection is the name for selecting fields. Think about what one row of the requested result should mean before writing the select list.

For this report the grain is one site-kind pair.

A SELECT list can contain stored columns or expressions. AS gives an expression a useful output label. For example, active AS is_active labels the returned value without renaming the stored column. Aliases are part of the result interface another program sees.

SELECT * is convenient for a first inspection, but it makes a long-lived report depend on every source column. A new database field can unexpectedly appear in an export. Naming columns also makes the intended grain easier to review.

An alias changes a result label; it does not change the underlying values.

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, active AS is_active
FROM assets
ORDER BY asset_id;

SELECT DISTINCT site, kind removes rows that repeat both selected values. A repeated site alone is insufficient, because north has a fan and a pump. A repeated kind alone is also insufficient, because pump occurs at several sites.

SQL retains duplicates by default. This differs from the mathematical idea of a relation as a set of tuples. Use DISTINCT when the question genuinely asks for unique combinations, not as a general way to make an unexpectedly large answer look tidier.

Duplicate removal can hide evidence of a faulty join. We will return to that problem when tables are connected.

An expression can calculate a value for each row, such as a duration in hours from stored minutes. Make its unit clear in the alias. In SQLite, dividing two integers can produce integer division, so use a real-valued operand when a fraction is needed.

Expressions do not automatically combine rows. A calculation in the SELECT list usually produces one value per surviving row; aggregation is a separate operation that comes later.

The decimal operand requests fractional arithmetic. A missing duration still produces a missing derived value.

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 inspection_id, ROUND(minutes / 60.0, 2) AS hours FROM inspections ORDER BY inspection_id;

DISTINCT determines which combinations survive. ORDER BY determines how to present them. Those are different requests, so do not depend on the apparent order a database happens to use while eliminating duplicates.

Our challenge sorts by site and then kind. The workshop deliberately includes a duplicate pair under a new asset ID; a construction that keeps the ID through duplicate removal will fail.

Read the output as a set of site-category facts displayed in a chosen order.