MySQL 5.7 – Adding a Running Count of Distinct Values

MySQLmysql-5.7window functions

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?

Best Answer

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

Related Question