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

6 votes229 views2 answers
What is non relational database
SQL
2 votes227 views1 answer
Oracle list grants on package
SQL
2 votes237 views1 answer
Oracle block
SQL
0 vote225 views1 answer
SQLSTATE[42S02]: Base table or view not found: 1146 Tabl
SQL
0 vote219 views1 answer
Query to count the number of rows in a table in sqlalchemy
SQL
0 vote223 views1 answer
Mysql min value row
SQL
3 votes206 views1 answer
Sql timestamp to date
SQL
0 vote207 views1 answer
Google sheets filter rows above current cell
SQL
2 votes212 views1 answer
Sql like variable
SQL
1 vote235 views1 answer
Oracle get current schema
SQL