Skip to main content
Code Review

Return to Question

removed the grouping on 'status', that leads to double rows once with connected count and once disconnected
Source Link
picklepick
picklepick
  • 155
  • 7

I'm trying to refresh my very rusty SQL skills.

I have a table with device logs, collected every 5 minutes. I'm interested in counting the number of times a device is either 'connected' or 'disconnected' per device, per day.

deviceId,  status,  tag,   timestamp
A,  Connected,    connectivity,  2022-10-27T09:25:02Z
B,  Connected,    connectivity,  2022-10-27T09:25:02Z
C,  Disconnected, connectivity,  2022-10-27T09:25:02Z
D,  Disconnected, connectivity,  2022-10-27T09:25:02Z
E,  Disconnected, connectivity,  2022-10-27T09:25:02Z
A,  Connected,    connectivity,  2022-10-27T09:30:02Z
B,  Connected,    connectivity,  2022-10-27T09:30:02Z
C,  Disconnected, connectivity,  2022-10-27T09:30:02Z
D,  Disconnected, connectivity,  2022-10-27T09:30:02Z
E,  Disconnected, connectivity,  2022-10-27T09:30:02Z
A,  Connected,    connectivity,  2022-10-27T09:35:02Z
B,  Connected,    connectivity,  2022-10-27T09:35:02Z
C,  Disconnected, connectivity,  2022-10-27T09:35:02Z
D,  Disconnected, connectivity,  2022-10-27T09:35:02Z
E,  Disconnected, connectivity,  2022-10-27T09:35:02Z
A,  Connected,    connectivity,  2022-10-27T09:48:10Z

I came up with the following SQL, since it's been a while, I was wondering if anyone has some feedback or alternative ideas.

SELECT 
    COUNT(CASE WHEN status = 'Connected' THEN 1 END) connectedCount,
    COUNT(CASE WHEN status = 'Disconnected' THEN 1 END) disconnectedCount, deviceId, 
    FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd') as date
FROM Reporting
WHERE tag = 'connectivity'
GROUP BY deviceId, status, FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd')
ORDER BY date, deviceId

I'm trying to refresh my very rusty SQL skills.

I have a table with device logs, collected every 5 minutes. I'm interested in counting the number of times a device is either 'connected' or 'disconnected' per device, per day.

deviceId,  status,  tag,   timestamp
A,  Connected,    connectivity,  2022-10-27T09:25:02Z
B,  Connected,    connectivity,  2022-10-27T09:25:02Z
C,  Disconnected, connectivity,  2022-10-27T09:25:02Z
D,  Disconnected, connectivity,  2022-10-27T09:25:02Z
E,  Disconnected, connectivity,  2022-10-27T09:25:02Z
A,  Connected,    connectivity,  2022-10-27T09:30:02Z
B,  Connected,    connectivity,  2022-10-27T09:30:02Z
C,  Disconnected, connectivity,  2022-10-27T09:30:02Z
D,  Disconnected, connectivity,  2022-10-27T09:30:02Z
E,  Disconnected, connectivity,  2022-10-27T09:30:02Z
A,  Connected,    connectivity,  2022-10-27T09:35:02Z
B,  Connected,    connectivity,  2022-10-27T09:35:02Z
C,  Disconnected, connectivity,  2022-10-27T09:35:02Z
D,  Disconnected, connectivity,  2022-10-27T09:35:02Z
E,  Disconnected, connectivity,  2022-10-27T09:35:02Z
A,  Connected,    connectivity,  2022-10-27T09:48:10Z

I came up with the following SQL, since it's been a while, I was wondering if anyone has some feedback or alternative ideas.

SELECT 
    COUNT(CASE WHEN status = 'Connected' THEN 1 END) connectedCount,
    COUNT(CASE WHEN status = 'Disconnected' THEN 1 END) disconnectedCount, deviceId, 
    FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd') as date
FROM Reporting
WHERE tag = 'connectivity'
GROUP BY deviceId, status, FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd')
ORDER BY date, deviceId

I'm trying to refresh my very rusty SQL skills.

I have a table with device logs, collected every 5 minutes. I'm interested in counting the number of times a device is either 'connected' or 'disconnected' per device, per day.

deviceId,  status,  tag,   timestamp
A,  Connected,    connectivity,  2022-10-27T09:25:02Z
B,  Connected,    connectivity,  2022-10-27T09:25:02Z
C,  Disconnected, connectivity,  2022-10-27T09:25:02Z
D,  Disconnected, connectivity,  2022-10-27T09:25:02Z
E,  Disconnected, connectivity,  2022-10-27T09:25:02Z
A,  Connected,    connectivity,  2022-10-27T09:30:02Z
B,  Connected,    connectivity,  2022-10-27T09:30:02Z
C,  Disconnected, connectivity,  2022-10-27T09:30:02Z
D,  Disconnected, connectivity,  2022-10-27T09:30:02Z
E,  Disconnected, connectivity,  2022-10-27T09:30:02Z
A,  Connected,    connectivity,  2022-10-27T09:35:02Z
B,  Connected,    connectivity,  2022-10-27T09:35:02Z
C,  Disconnected, connectivity,  2022-10-27T09:35:02Z
D,  Disconnected, connectivity,  2022-10-27T09:35:02Z
E,  Disconnected, connectivity,  2022-10-27T09:35:02Z
A,  Connected,    connectivity,  2022-10-27T09:48:10Z

I came up with the following SQL, since it's been a while, I was wondering if anyone has some feedback or alternative ideas.

SELECT 
    COUNT(CASE WHEN status = 'Connected' THEN 1 END) connectedCount,
    COUNT(CASE WHEN status = 'Disconnected' THEN 1 END) disconnectedCount, deviceId, 
    FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd') as date
FROM Reporting
WHERE tag = 'connectivity'
GROUP BY deviceId, FORMAT(CAST(timestamp AS DATE), 'yyyy-MM-dd')
ORDER BY date, deviceId
Made the title more descriptive.
Link
edit approved Nov 3, 2022 at 13:27
Syed M. Sannan
edit approved Nov 3, 2022 at 13:27
Syed M. Sannan
  • 38
  • 17

Can this SQL Query for Grouping on Multiple Columns and Date be Improved in any way?

edited title
Link
picklepick
picklepick
  • 155
  • 7

SQL Query GroupinGrouping on Multiple Columns and Date

edited title
Link
picklepick
picklepick
  • 155
  • 7
Source Link
picklepick
picklepick
  • 155
  • 7