sql join with count of other table code example

Example 1: mysql select and count left join

select
  t.Topic,
  t.Title,
  count(distinct s.starID) as StarCount,
  count(distinct m.User) as UserCount,
  count(distinct m.messageID) as MessageCount
from
  Topics t
  left join Messages m ON m.Topic = t.Topic
  left join Stars_Given s ON s.Topic = t.Topic
group by
  t.Topic,
  t.Title

Example 2: sql query for getting data with join and count

Correlated subquery

SELECT job_Desc
 ,(select count(*) from employee where employee.job_id = jobs.job_id) as count
FROM Jobs
ORDER BY 2

refrence:
https://azagappan.wordpress.com/2006/06/23/using-count-with-joins/#:~:text=COUNT(*)%20returns%20the%20number,number%20of%20unique%2C%20nonnull%20values.

Tags:

Sql Example

Related