1

I have a table with these three columns (plus some others that are not relevant):

connector_pk, group_status and status_timestamp

There are 3 different types of group_status and the connector_pk can be any one of them, but only one at a time. I would like to count the number of connector_pks for each group_status based on their latest status_timestamp.

Example table

connector_pk    group_status    status timestamp
1               Available       2020-02-11 19:14:45
1               Charging        2020-02-11 19:18:45
2               Available       2020-02-11 19:15:45
2               Not Available   2020-02-11 19:18:45
3               Not Available   2020-02-11 19:14:45

The desired output would look like this:

group_Status      | Count of status    
Available         | 0    
Charging          | 1    
Not Available     | 2

I have used the following code, but it doesn't provide a count for each connector_pk or create a new table:

select group_status, count(group_status)
   from connector_status_report
   where status_timestamp in (
select  max(status_timestamp)
from connector_status_report
)
group by group_status
RDC_Green
  • 57
  • 5
  • please add the data as text not as image also it should correspond to your wanted result – nbk Oct 18 '20 at 17:26
  • thanks nbk, hopefully that's clearer now – RDC_Green Oct 18 '20 at 18:17
  • I do not understand how you obtain the `Count of status` value – Radim Bača Oct 18 '20 at 18:26
  • There are 5 connector_pks in total, so the Count of status will always add up to 5. What i'm trying to do is show in real time (latest timestamp) how many of each connector_pk sites within each group_status. So previous status_timestamps are ignored, it just tries to find the latest one for each connector_pk – RDC_Green Oct 18 '20 at 18:29
  • as i can't answer anymore take a look https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=1efe75d0b31fdd35ce8646c96b44d6fc – nbk Oct 18 '20 at 18:33
  • would it be more efficient to create a table that only pulls through the latest status_timestamp for each connector_pk and then do the count of group – RDC_Green Oct 18 '20 at 19:13
  • @nbk I've just worked out how to quote somebody. Not sure if you got my queries above due to this question being closed. Also not sure how to vote now that the question has been closed. Thank you for your help – RDC_Green Oct 18 '20 at 20:34
  • no worries @RDC_Green glad it helped – nbk Oct 18 '20 at 20:36

0 Answers0