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 vote340 views1 answer
Apache Derby: Create SQL Dump with data
SQL
14 votes342 views4 answers
Remove record from table sql
SQL
0 vote368 views2 answers
List all constraints in postgres
SQL
33 votes333 views2 answers
Postgres how to add field created at
SQL
0 vote337 views1 answer
Dapper : operator does not exist: integer = integer[]'
SQL
0 vote279 views1 answer
Oracle function select into
SQL
0 vote344 views1 answer
Asp.net core sql server stored procedure
SQL
0 vote302 views1 answer
Count column of tables psql
SQL
20 votes346 views6 answers
Mysql reset auto increment
SQL
1 vote349 views1 answer
Spark sql concatenate string and int
SQL