How to remove doubles in output?

Question

MySQL

Hello. I have a table that stores records of turning on and off all devices. During the day, the device may turn on and off several times. I need to display the number of devices that were turned off per day during the week. The table has the following structure:
id, name, ip, type, time, status
I made a query like this:

SELECT COUNT(id) AS amount, time, status FROM info WHERE time > DATE_SUB(NOW(), INTERVAL 7 DAY) AND status = :status GROUP BY EXTRACT(DAY FROM time) ORDER BY time

It outputs the count, but I need to count once the device per day. Thanks in advance.

Answer 1

SELECT COUNT(DISTINCT id) ... --подсчет всех уникальных значений. Чтобы убедиться, что все корректно, сначала запросите
SELECT DISTINCT id ... -- с вашим условием

Answer 2

для начала вытяните последние (MAX(time)) выключения - а потом уже по ним делайте count