Mysql count with two joins

select
  t.Topic,
  t.Title,
  s.StarCount,
  m.UserCount,
  m.MessageCount
from
  Topics t
  left join (
    select 
      Topic, 
      count(distinct User) as UserCount,
      count(*) as MessageCount
    from Messages
    group by Topic
  ) m ON m.Topic = t.Topic
  left join (
    select
      Topic, 
      count(*) as StarCount
    from Stars_Given 
    group by Topic
  ) s ON s.Topic = t.Topic

Source: stackoverflow.com

Similar codes

2 votes208 views1 answer
Postgresql drop primary key constraint
SQL
0 vote229 views1 answer
Hour differeence in mysql
SQL
0 vote209 views1 answer
Reindex mssql table
SQL
0 vote199 views1 answer
Sql contains numeric characters
SQL
4 votes217 views1 answer
Is there any command to change postgres password
SQL
0 vote208 views1 answer
How to fetch unique records from two tables
SQL
21 votes215 views6 answers
Update sql
SQL
0 vote218 views1 answer
Sqlalchemy empty table
SQL
2 votes198 views1 answer
Anonymous block plsql
SQL
0 vote196 views1 answer
Sql server update top n records
SQL