CODEWITHSUNDEEP
mysqlsql
jacksonFive5
jacksonFive5

Reputation: 31

performing mathematical operations on the query

I'm using a query to return the total number of records x number of days that were registered... I would like to have the average too, how can I get this result?

SELECT COUNT(DISTINCT CAST(DataCriacao AS DATE))  AS dias, userid, COUNT(*), SUM(count(*) / dias) FROM ticket WHERE MONTH(DataCriacao) = month(CURDATE()) AND YEAR(DataCriacao) = YEAR(CURDATE()) AND motivoRegistro LIKE '%Ap%' GROUP BY userid ORDER BY COUNT(*) DESC

expected

+--------+------+----------+-----+
| userid | dias | count(*) | avg |
+--------+------+----------+-----+
| root   |   10 |       50 |   5 |
+--------+------+----------+-----+

Upvotes: 1

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Do you just want division?

SELECT COUNT(DISTINCT CAST(DataCriacao AS DATE)) AS dias, userid, COUNT(*),
       count(*) / COUNT(DISTINCT CAST(DataCriacao AS DATE))
FROM ticket
WHERE MONTH(DataCriacao) = month(CURDATE()) AND
      YEAR(DataCriacao) = YEAR(CURDATE()) AND
      motivoRegistro LIKE '%Ap%'
GROUP BY userid
ORDER BY COUNT(*) DESC

Upvotes: 1

Related Questions