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 vote324 views1 answer
Initcap in mysql
SQL
1 vote332 views1 answer
Raise application error in oracle
SQL
0 vote324 views1 answer
Ran rails db:migrate and got == 20210620104415 DeviseCreateUsers: migrating ================================ -- create_table(:users) rails aborted! StandardError: An error has occurred, this and all later migrations canceled: SQLite3::SQLE
SQL
3 votes353 views4 answers
Postgresql change timezone on timestamp without timezone
SQL
0 vote341 views1 answer
Geopoint from json mysql function
SQL
2 votes330 views1 answer
Guid to string sql
SQL
0 vote340 views1 answer
How to find table lock and row lock in mysql
SQL
0 vote329 views1 answer
Postgres type equivalent to timespan c#
SQL
0 vote331 views1 answer
Migrations.RunSQL
SQL
10 votes326 views1 answer
Oracle object dependencies
SQL