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 vote269 views1 answer
Oracle service name view
SQL
-1 vote275 views1 answer
Mysql change database charset and collation
SQL
0 vote364 views1 answer
Compare strings lexicographically in sql
SQL
0 vote259 views1 answer
Check mysql password with docker container magento 2
SQL
0 vote276 views1 answer
Postgres drop type
SQL
59 votes450 views6 answers
Alter table sql
SQL
1 vote301 views1 answer
Set id count mysql
SQL
11 votes262 views1 answer
How to get ddl for materialized view
SQL
0 vote278 views1 answer
Copy Table from SQL to CSV
SQL
2 votes286 views1 answer
Sqlite3 show columns name
SQL