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 votes167 views2 answers
Oracle
SQL
-1 vote177 views1 answer
Mysql select all columns and specific fields as
SQL
0 vote168 views1 answer
Stored procedures example
SQL
8 votes202 views2 answers
Select current_timestamp - interval '3 days';
SQL
0 vote196 views1 answer
Rails sql query converstion
SQL
0 vote266 views3 answers
Get count of null in column sql
SQL
1 vote158 views1 answer
Jooq convert using gbk
SQL
0 vote205 views1 answer
Sql convert float to string
SQL
0 vote183 views1 answer
Create table with float datatype in sql server
SQL
11 votes189 views5 answers
Sql server reset identity
SQL