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

0 vote148 views1 answer
Delete insert record in sql server
SQL
0 vote147 views1 answer
How to find username and password in oracle 18c
SQL
1 vote149 views1 answer
Sql headers delphi
SQL
0 vote228 views1 answer
How to show current database in mysql
SQL
1 vote140 views1 answer
Foreign key constraint in ms sql
SQL
0 vote151 views1 answer
Oracle right characters
SQL
8 votes195 views6 answers
Insert command in sql
SQL
1 vote168 views1 answer
Mysql dump specific tables
SQL
0 vote179 views2 answers
Nested query
SQL
0 vote158 views1 answer
Write SQL in ruby on rails
SQL