Count devices per day in a given date range

I have a table which has devices with 3 statuses, Pass, Fail and Warning.

Table Columns: overall_status, DateTime, DeviceName

I want to generate a trend graph of count of devices based on the daily status. The count is on all the devices for each day. The table above will have device data repeated for multiple dates. Example Device1 12/1/1 Fail Device2 12/1/1 Pass Device3 12/1/1 Pass Device4 12/1/1 Warning Device1 12/2/2 Pass Device2 12/2/2 Pass Device3 12/2/2 Warning Device4 12/2/2 Fail

I want to generate a stacked bar graph, which will show count of devices which are pass, fail or warning. Need to get a query which I can use to get the response back with DateTime, count of failed devices, count of devices passed, count of devices having warning over a range of dates.

select device_name, (select count(*) from status_table where overall_status = 'Fail' and startDate > "" and endDate < "") as failedCount,  (select count(*) from status_table  where overall_status = 'Warning' and startDate > "" and endDate < "") as WarningCount,  (select count(*) from status_table  where overall_status = 'Pass'  startDate > "" and endDate < "") as passCount from status_table  

Is there a better solution?

https://stackoverflow.com/questions/65929258/count-devices-per-day-in-a-given-date-range January 28, 2021 at 08:11AM