SQL help with count after group by

[rh71]

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:

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:

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.

 

[mooncancook]

I don't see any reason why count doesn't work here. Just try a simple `
SELECT DeviceModel, COUNT(DeviceModel) AS theCount FROM Device GROUP BY DeviceModel`, you should get count per model. Should be no different if you add the COUNT to your existing SQL

 

[Cogman]

You can, in fact, just add `Count(1)` or `count(*)` and that will work with the group by statement to count each row present in the group.