Query with Count returning empty

Question

I have the following appointment:

SELECT 
  numero,
  sistema,
  status,
  date_first_queued
  FROM 
    ocorrencias 
      HAVING DATE(date_first_queued) between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE()
      AND  sistema = '17'

She returns me the records perfectly, but I want to use together with her a count only then it returns empty, follows my query with count:

SELECT
 COUNT(numero), 
  numero,
  sistema,
  status,
  date_first_queued
  FROM 
    ocorrencias 
      HAVING DATE(date_first_queued) between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE()
      AND  sistema = '17'

I even need to use the group by more when I put the group by of the following error below:

Answer 1

When a grouping function is used (SUM, COUNT, MIN, MAX, ...) if there is one more field beyond the field being grouped, A GROUP BY clause MUST be added, always before ORDER BY (when it exists).

In your case, it is missing to add the other fields beyond the COUNT field in the GROUP BY clause, besides this being in the wrong place.

The correct query would be:

SELECT
  COUNT(numero) contador, 
  numero,
  sistema,
  status,
  date_first_queued
FROM ocorrencias 
WHERE DATE(date_first_queued) between  DATE_FORMAT(CURDATE() ,'%Y-%m-01') AND CURDATE() AND  sistema = '17'
GROUP BY
  numero, 
  sistema,
  status,
  date_first_queued

That is, you want to count how many records there are in the OCCURRENCES table with the same numero, sistema, status, date_first_queued.