CODEWITHSUNDEEP
mysql
Andrey
Andrey

Reputation: 95

How to calculate AVG, MAX and MIN number of rows in a column

I try to collect general statistics on the depth of correspondence: average, maximum and minimum number of messages of each type per one request. Have 2 tables:

First:
ticketId,ticketQueueId,ticketCreatedDate

Second:
articleId,articleCreatedDt,articleType (can be IN or OUT - support responses), ticketId

I reasoned like this:

SELECT AVG(COUNT(articleType='IN')) AS AT_IN, AVG(COUNT(articleType='OUT')) AS AT_OUT 
FROM tickets.tickets JOIN tickets.articles
ON tickets.ticketId=articles.ticketId;
GROUP BY tickets.ticketId

but it doesn't work.

Error Code: 1111. Invalid use of group function

Upvotes: 0

Views: 38

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

you can't use nested aggregation function (AVG(COUNT())) but use proper subquery and apply the aggregation function the the subquery gradually

also your use of of count in improper

the count function count each row where the related column is not null so in your case the evaluation articleType='IN' (or articleType='OUT') returning 0 or 1 is never null

select AVG(T_IN), AVG(T_OUT)
from (
    SELECT sum(case when articleType='IN' then 1 else 0 END  AS T_IN, sum(case when articleType='OUT' then 1 else 0 END  AS T_OUT 
    FROM tickets.tickets 
    JOIN tickets.articles ON tickets.ticketId=articles.ticketId
    GROUP BY tickets.ticketId

) t

(and You have also a wrong semicolon )

Upvotes: 1

Related Questions