Sum values from a column in the table where the date repeats, in a SUB-SELECT

Question

Dates are different in your hours as this example:

+---------------------+-------------------+-------+
| DATA                | callid            | tempo |
+---------------------+-------------------+-------+
| 2021-08-01 12:23:50 | 1627831011.78021  |   345 |
| 2021-08-01 17:08:26 | 1627848459.79020  |    12 |
| 2021-08-03 08:58:24 | 1627905500.80140  |     2 |
| 2021-08-04 09:28:01 | 1627907135.80426  |   114 |

I wanted to add and make the media with these first 2 records of 2021-08-01, that is only with the days that repeat.

I tried to use the AVG()(to measure the domed values) and SUM() but does not work from the following error: ERROR 1111 (HY000): Invalid use of group function

SELECT c.DATA,
       c.callid,
       AVG(SUM(c.tempo)) AS tempo_medio
FROM   (SELECT q.calldate     AS DATA,
               callid,
               ROUND(q.info2) AS tempo
        FROM   queuelog AS q
               LEFT JOIN asteriskcdrdb.cdr o
                      ON o.uniqueid = q.callid
               LEFT JOIN cdrjuncao cj
                      ON o.uniqueid = cj.uniqueid
                         AND o.calldate = cj.calldate
        WHERE  timestamp BETWEEN '1627786800' AND '1630551599'
               AND ( agent = '9043'
                      OR agent = '9043' )
               AND ( qname = '717'
                      OR qname = '721'
                      OR qname = '701' )
               AND action IN ( 'COMPLETEAGENT', 'COMPLETECALLER', 'TRANSFER' )
               AND o.uniqueid NOT IN ( '' )) c
GROUP  BY c.DATA;

Can someone help me? vlwss

Answer 1

Nor will I enter the merit of trying to understand what the tables are queuelog, asteriskcdrdb.cdr and cdrjuncao, because it is not clear what is their relationship with the problem.

You did GROUP BY c.DATA, but as the column has the time, it will be grouped every second and not by date. The code does not reflect your intention.

And did also AVG(SUM(c.tempo)). Did that make sense to you? The function AVG already makes the sum and divides by the quantity, which is the definition of the arithmetic mean, so do not need to add before. The average of 2 and 4 is 3, but what would be the average of 6, which is the sum of the two?

Finally, the code would simply look like:

SELECT DATE(`DATA`), AVG(`tempo`)
FROM tempos
GROUP BY DATE(`DATA`)

Produces a result similar to:

DATE(`DATE`)	AVG(`time`)
2021-08-01	178.5000
2021-08-03	2.0000
2021-08-04	114.0000