Machine Count per Plant From Asset Tags

Easy
252.

Machine Count per Plant From Asset Tags

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

ColumnType
asset_tagvarchar
modelvarchar
commissioned_ondate

Primary key: asset_tag.

One row per machine. Plant codes are always three upper-case letters; the number part is four digits.

Example

The rows must come back in the order shown.

AssetRegister

asset_tagmodelcommissioned_on
PUN-CNC-0042Haas VF-22021-03-14
PUN-PRS-0007Schuler 250T2019-08-02
CHN-CNC-0011Mazak VCN-5302022-11-21
AMD-WLD-0003Lincoln 350MP2023-01-09
CHN-LTH-0005Ace LT-202020-06-30
PUN-WLD-0019Lincoln 350MP2024-02-12
AMD-CNC-0020Haas VF-22024-05-05

Output

plant_codemachine_count
PUN3
AMD2
CHN2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
asset_tagvarchar
modelvarchar
commissioned_ondate

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_tagmodelcommissioned_on
PUN-CNC-0042Haas VF-22021-03-14
PUN-PRS-0007Schuler 250T2019-08-02
CHN-CNC-0011Mazak VCN-5302022-11-21
AMD-WLD-0003Lincoln 350MP2023-01-09
CHN-LTH-0005Ace LT-202020-06-30
PUN-WLD-0019Lincoln 350MP2024-02-12
AMD-CNC-0020Haas VF-22024-05-05

Output

plant_codemachine_count
PUN3
AMD2
CHN2

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_code

Another 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, 1

Another 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 →