Machine Count per Plant From Asset Tags — SQL Problem
The company's fixed-asset register gives every machine an asset tag of the form PLANT-TYPE-NUMBER, such as PUN-CNC-0042: the part before the first hyphen is…
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #252
Problem statement
The company's fixed-asset register gives every machine an asset tag of the form PLANT-TYPE-NUMBER, such as PUN-CNC-0042: the part before the first hyphen is the plant code. The register has no plant column of its own.
Return each plant code with the number of machines tagged to it, as plant_code and machine_count. Order the rows by machine_count from highest to lowest, then by plant_code alphabetically.
Tables
Table: AssetRegister
| Column | Type |
|---|---|
| asset_tag | varchar |
| model | varchar |
| commissioned_on | date |
Primary key: asset_tag.
One row per machine. Plant codes are always three upper-case letters; the number part is four digits.
Examples
Example 1
AssetRegister
| asset_tag | model | commissioned_on |
|---|---|---|
| PUN-CNC-0042 | Haas VF-2 | 2021-03-14 |
| PUN-PRS-0007 | Schuler 250T | 2019-08-02 |
| CHN-CNC-0011 | Mazak VCN-530 | 2022-11-21 |
| AMD-WLD-0003 | Lincoln 350MP | 2023-01-09 |
| CHN-LTH-0005 | Ace LT-20 | 2020-06-30 |
| PUN-WLD-0019 | Lincoln 350MP | 2024-02-12 |
| AMD-CNC-0020 | Haas VF-2 | 2024-05-05 |
Output
| plant_code | machine_count |
|---|---|
| PUN | 3 |
| AMD | 2 |
| CHN | 2 |
How to solve Machine Count per Plant From Asset Tags
There is no plant column, so the grouping key has to be computed from the tag. SUBSTRING_INDEX(asset_tag, '-', 1) returns the text before the first hyphen — PUN for PUN-CNC-0042 — and grouping by that expression puts every machine of a plant in one group, where COUNT(*) counts them.
MySQL lets the GROUP BY repeat the expression, or you can compute the code in a derived table and group by its column; both are the same plan. Because plant codes are always three letters, LEFT(asset_tag, 3) gives the same key — but it silently breaks the day a four-letter plant is added, which is why splitting on the delimiter is the safer habit. LOCATE + SUBSTRING is the long-hand form of the same split.
The order is by count, highest first, and plants with equal counts are listed alphabetically so the output is fixed. One scan, one hash aggregation over a handful of plants.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(asset_tag, '-', 1) AS plant_code, COUNT(*) AS machine_count
FROM AssetRegister
GROUP BY SUBSTRING_INDEX(asset_tag, '-', 1)
ORDER BY machine_count DESC, plant_codeAnother way
SELECT LEFT(asset_tag, 3) AS plant_code, COUNT(*) AS machine_count FROM AssetRegister GROUP BY LEFT(asset_tag, 3) ORDER BY 2 DESC, 1Another way
SELECT plant_code, COUNT(*) AS machine_count
FROM (SELECT SUBSTRING(asset_tag, 1, LOCATE('-', asset_tag) - 1) AS plant_code FROM AssetRegister) t
GROUP BY plant_code ORDER BY machine_count DESC, plant_code← Production Batches Over Two Percent Defects · EV Charging Energy Delivered per Station →