Count from latest timestamps only

Telmo 0
Viewed 47

I'm trying to count latest actions from devices in my table:

device action timestamp
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

I'm trying :

SELECT
    count(device)
FROM
    table
WHERE
    action='shutdown'
    AND
    timestamp=(SELECT max(timestamp) FROM table)
;

But can't get it to work. Expected result :

  • when querying shutdown: 0
  • when querying running: 2
  • when querying starting: 1

All I get is the full count of the actions.

timestamp sqlite count
2 Answers

you need to create a key based on concatenating 2 columns (device and time stamp when the timestamp is max)

see the code below:

select count(*) from diario where device || timestamp in (select device || mx from(select device,max(timestamp) mx from diario group by device)) and action = "shutdown";

the result is 0 when action is shutdown, 2 running, 1 starting

karam yakoub agha0

If you want results for all actions then you can do a LEFT join of the distinct actions to a query that returns the last timestamp for each device and aggregate:

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;

Results:

action count
running 2
shutdown 0
starting 1

If you want results for only one type of action:

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

Replace ? with the action that you want.

See the demo.

forpas0
Related