CODEWITHSUNDEEP
phpmysqlsum
Anonymous
Anonymous

Reputation: 25

Total comments from multiple rows MYSQL

I have to sort the movies by comments so.. I tried something but I failed.

Well...

I have 2 tables

1 - movies

id -- name
 1     XX
 2     YY

2 - episodes

mid -> movie id from first table

eid -> episode id

id -- mid -- eid -- comments
 1     1      1       4
 2     1      2       2
 3     1      3       2
 4     2      1       1
 5     2      2       1

and I tried with 

$q = mysql_query("SELECT SUM(comments) AS sum_comments, COUNT(mid) AS count_mid FROM episodes ORDER BY sum_comments DESC");
while($arr = mysql_fetch_array($q))
{
    echo $arr['count_mid']." - ".$arr['sum_comments']."<br/>";
}

Output: 5 - 10

Excepted: 1 - 8
          2 - 2

Thank you very much !!

Upvotes: 1

Views: 101

Answers (2)

Rajat Singhal
Rajat Singhal

Reputation: 11264

SELECT mid,sum(comments) AS no_comment FROM episodes 
GROUP BY mid ORDER BY no_comment..

Upvotes: 1

Shyju
Shyju

Reputation: 218832

select mid,sum(comments) as TotalCommentCount from episodes 
group by mid   order by  mid

Upvotes: 4

Related Questions