SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (`device` INTEGER, `action` VARCHAR(8), `timestamp` TEXT);
✓
INSERT INTO tablename (`device`, `action`, `timestamp`) VALUES
  ('1', 'running', '2022-09-12 16:20:10'),
  ('1', 'shutdown', '2022-09-10 16:20:10'),
  ('2', 'running', '2022-09-12 16:20:10'),
  ('2', 'starting', '2022-09-11 16:20:10'),
  ('3', 'starting', '2022-09-11 16:20:10');
✓
SELECT a.action, COUNT(t.action) count
FROM (SELECT DISTINCT action FROM tablename) a
LEFT JOIN (SELECT action, MAX(timestamp) FROM tablename GROUP BY device) t
ON t.action = a.action
GROUP BY a.action;


action
count


running
2

shutdown
0

starting
1


SELECT DISTINCT SUM(action = 'running') OVER () count
FROM tablename
GROUP BY device
HAVING MAX(timestamp);


count


2


SELECT DISTINCT SUM(action = 'shutdown') OVER () count
FROM tablename
GROUP BY device
HAVING MAX(timestamp);


count


0


SELECT DISTINCT SUM(action = 'starting') OVER () count
FROM tablename
GROUP BY device
HAVING MAX(timestamp);


count


1