Select only 24 records per day

Question

I need help to elaborate a query that selects only 24 records per day.

For example:

Day 1: 100 records
Day 2: 50 records
Day 3: 34 records

I want to take only 24 of each day: 1, 2 and 3...(in this example I would add: 72 records)

SELECT date_format(hora,'%d/%m/%Y'), SUM(ponto) FROM `pontos` WHERE user = "teste" AND hora >= '2019-08-20' AND hora < NOW() 

*As it stands, my query brings the sum of all records

What’s the best way to do that?

Answer 1

Use a subquery with LIMIT 24. Create an alias for date_format(hora,'%d/%m/%Y') and use GROUP BY in that alias to return all dates with the result of SUM(ponto):

SELECT date_format(hora,'%d/%m/%Y') AS dia, SUM(ponto) FROM
(SELECT hora, ponto
      FROM `pontos`
      WHERE user = "teste" AND hora >= '2019-08-20' AND hora < NOW()
      LIMIT 24
) AS subquery
GROUP BY dia

Check on the SQLFIDDLE

Answer 2

You can use the LIMIT:

SELECT date_format(hora,'%d/%m/%Y'), SUM(ponto)
FROM pontos
WHERE user = "teste"
  AND hora >= '2019-08-20'
  AND hora < NOW()
LIMIT 24