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 votes225 views1 answer
Node and mysql like
SQL
0 vote210 views1 answer
Oracle procedure chain step
SQL
3 votes214 views1 answer
Sql double index
SQL
3 votes262 views2 answers
Funktion LOWER/UPPER sql
SQL
3 votes197 views1 answer
Sql case update
SQL
0 vote272 views1 answer
Dynamic where clause in sql server stored procedure
SQL
0 vote223 views1 answer
Postegresql update to null
SQL
0 vote225 views1 answer
InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
SQL
16 votes189 views2 answers
Add column to all tables after first column mysql
SQL
0 vote230 views1 answer
Sqlalchemy empty table
SQL