0

I have a table that records which hardware was used for a given run (job / batch) on a tool. Vastly simplified, the table looks like:

CREATE TABLE hardware (
  `run_name` varchar(8) NOT NULL,
  `hardware_used` varchar(45) DEFAULT NULL,
PRIMARY KEY (`run_name`)
);
run_name   hardware_used
pk123          h1
pk124          h1
pk125          h2
pk126          h2
pk127          h2
pk128          h3
pk129          h1

I want to get a running count of how many times each hardware has been used:

run_name   hardware_used   times_used
pk123          h1              1
pk124          h1              2
pk125          h2              1
pk126          h2              2
pk127          h2              3
pk128          h3              1
pk129          h1              3

In lexicographical order of run_name, create a running tally of how many times a given hardware_used has been seen.

In SQL Server, PostgreSQL, and MySQL 8, I think it could be done using Window Functions. Eg: the using SQL Server's OVER clause and Partitions, but I'm stuck with using MySQL 5.7.

Is something like this possible to generate in pure SQL?

2
  • @Akina I'm not sure I follow. The table with run_name, hardware_used and times_used is exactly the result I'm looking for. What part are you claiming does not match fully?
    – dthor
    Jun 2, 2020 at 14:01
  • "where is times_used = 3 for hardware_used = 1?" Ah, yes that's a typo. The final row should have times_used=3, not times_used=4. Sorry about that. I've updated the Q to fix that and also added a note about ordering
    – dthor
    Jun 2, 2020 at 16:59

1 Answer 1

1
SELECT t1.run_name, hardware_used, COUNT(*) times_used
FROM hardware t1
JOIN hardware t2 USING (hardware_used)
WHERE t1.run_name >= t2.run_name
GROUP BY t1.run_name, hardware_used
ORDER BY t1.run_name;

fiddle

1
  • Perfect! Very clever joining the table to itself. I normally only do that with self-referential tables - I didn't think to use that same idea here!
    – dthor
    Jun 2, 2020 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.