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 vote158 views1 answer
How to set all the time serveroutput on in oracle sql developer
SQL
0 vote154 views1 answer
Oracle archivemode
SQL
0 vote157 views1 answer
Mysql insert into select with recursive
SQL
0 vote162 views1 answer
How to drop a trigger in postgresql
SQL
6 votes170 views4 answers
Mysql get random row
SQL
3 votes111 views1 answer
Ubuntu install postgresql 12
SQL
4 votes223 views1 answer
Mysql store ip address
SQL
0 vote158 views1 answer
Psql check if sql script is valid
SQL
0 vote137 views1 answer
Find largest table in mysql database
SQL
15 votes152 views2 answers
Open postgress in terminal mac
SQL