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

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.

CodePudding user response：

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

CodePudding user response：

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:

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.