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 vote409 views1 answer
Change date in pivot table to month in sql server
SQL
2 votes368 views1 answer
Mysql now format
SQL
0 vote357 views1 answer
ORA-00942
SQL
2 votes362 views1 answer
Laravel sql String
SQL
1 vote361 views2 answers
Mysql allow remote connections
SQL
1 vote375 views1 answer
Postgresql not in
SQL
0 vote345 views1 answer
How to update rows from a table when certain conditions are met in mysql
SQL
5 votes363 views1 answer
Oracle create table comment
SQL
4 votes373 views3 answers
Check if two date ranges overlap sql server
SQL
0 vote380 views1 answer
Dump only data
SQL