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 votes178 views1 answer
Select from array in psql
SQL
1 vote130 views1 answer
Drop table if exists oracle
SQL
6 votes147 views3 answers
What is where in sql
SQL
0 vote140 views1 answer
Get first 3 letter of department name in sql
SQL
0 vote144 views1 answer
Mysql change timestamp on update
SQL
7 votes161 views2 answers
Mysql Public Key Retrieval is not allowed
SQL
1 vote141 views2 answers
Rebuild index sql server
SQL
0 vote143 views3 answers
Drop multiple columns in sql
SQL
0 vote139 views1 answer
Sql orcale i forgot what my name & password was
SQL
0 vote138 views1 answer
Contact mysql column field
SQL