- Aug 28, 2001
- 52,853
- 1,048
- 126
Have a Device table and a Batterylife table which captures the battery percentage of devices at the same time every day. I'm trying to display the average battery life per device as well as (and here's the issue) give a count of how many devices were captured within the time frame I'm trying to display.
Device Table:
Batterylife Table:
Current query:
Which produces:
Now I want to add a "count of devices" column (as theCount) that was included in the time constraint of the query (of 6/7/22 onward), which should look like this since only the iphones were recorded 2 times since 6/7/22:
I can't figure out how to adjust the above query since if I just add a select count(d.DeviceModel) in there, it actually counts all the rows regardless of the time period I queried. I'm thinking I need a subquery of some sort but what I've tried results in counting ALL the rows just the same.
Device Table:
DeviceID | DeviceModel |
1 | iphone 10 |
2 | iphone 13 |
3 | galaxy S22 |
4 | galaxy S9 |
Batterylife Table:
DeviceID | RecordedDateTime | BatteryPercent |
---|---|---|
1 | 6/9/22 12:00:00 | 88 |
2 | 6/9/22 12:00:00 | 94 |
3 | 6/9/22 12:00:00 | 96 |
4 | 6/9/22 12:00:00 | 93 |
1 | 6/7/22 12:00:00 | 66 |
2 | 6/7/22 12:00:00 | 72 |
1 | 6/5/22 12:00:00 | 55 |
2 | 6/5/22 12:00:00 | 43 |
3 | 6/5/22 12:00:00 | 66 |
4 | 6/5/22 12:00:00 | 67 |
Current query:
SQL:
SELECT d.DeviceModel, avg(b.BatteryPercent) as Percent
FROM Device d INNER JOIN Batterylife b on d.DeviceID = b.DeviceID
WHERE b.RecordedDateTime > '6/7/22 0:00:00'
GROUP BY d.DeviceModel
Which produces:
DeviceModel | Percent |
---|---|
iphone 10 | 77 |
iphone 13 | 83 |
galaxy S22 | 96 |
galaxy S9 | 93 |
Now I want to add a "count of devices" column (as theCount) that was included in the time constraint of the query (of 6/7/22 onward), which should look like this since only the iphones were recorded 2 times since 6/7/22:
DeviceModel | theCount | Percent |
---|---|---|
iphone 10 | 2 | 77 |
iphone 13 | 2 | 83 |
galaxy S22 | 1 | 96 |
galaxy S9 | 1 | 93 |
I can't figure out how to adjust the above query since if I just add a select count(d.DeviceModel) in there, it actually counts all the rows regardless of the time period I queried. I'm thinking I need a subquery of some sort but what I've tried results in counting ALL the rows just the same.
Last edited: