CODEWITHSUNDEEP
mysqlsql
Roi
Roi

Reputation: 109

How to average rows?

I have table that topic title, and another that contain replies (per topic)

How do I get the average replies per topic (by total)?

tkts_topics

id  open_by     status
12  4             3
2   2             3


tkts_replies

id  tkt_id  open_by     name    text
2    2       2          asaf    some text

Upvotes: 0

Views: 49

Answers (2)

forpas
forpas

Reputation: 164069

Join the tables and group by topic id to get the number of replies for each topic.
Then get the average of all these numbers:

select avg(g.counter) averagereplies from (
  select count(r.id) counter
  from tkts_topics t left join tkts_replies r
  on r.tkt_id = t.id 
  where t.status = 3
  group by t.id 
) g

See the demo.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You just need to divide counts from the two tables:

select ( (select count(*) from tkts_replies) /
         (select count(*) from tkts_topics)
       ) as avg_replies_per_topic

Upvotes: 1

Related Questions