Query with count returning empty

Question

Query with count returning empty

Navigation

#1 by (2 votes)

0

I have the following query:

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'

It returns me the records perfectly, but I want to use a count with it, except that it returns empty, it 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 group by more when I put group by of the following error below:

mysql

asked by anonymous 28.12.2016 / 15:05

1 answer

Insert buttons to share posts in Wordpress vue vue-resource $ http.get does not recognize json with [

score 2 · Accepted Answer

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

In your case, it is necessary to add the other fields besides the COUNT field in the GROUP BY clause, in addition to 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 table OCCURRENCES with the same numero, sistema, status, date_first_queued .