Join multiple tables, select counts from different tables and group by one column in one query

Question

I need to join multiple tables, select counts from different tables and group by one column in one query. This is how I would do this separately:

select      c.CommunityName, SUM(case when m.ListKey = c.ListKey then 1 else 0 end) as Posts
from        Community c with(NOLOCK)
join        messages_ m with(NOLOCK)
on          c.ListKey = m.ListKey
group by    c.CommunityName

select      c.CommunityName, SUM(case when b.CommunityKey = c.CommunityKey then 1 else 0 end) as Blogs
from        Community c with(NOLOCK)
join        Blog b with(NOLOCK)
on          c.CommunityKey = b.CommunityKey
group by    c.CommunityName

select      c.CommunityName, SUM(case when ce.CommunityKey = c.CommunityKey then 1 else 0 end) as Events
from        Community c with(NOLOCK)
join        CalendarEvent ce with(NOLOCK)
on          c.CommunityKey = ce.CommunityKey
where       ce.StartDateTime >= GETDATE()
group by    c.CommunityName

or simply

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        messages_ m with(NOLOCK)
on          c.ListKey = m.ListKey
group by    c.CommunityName

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        Blog b with(NOLOCK)
on          c.CommunityKey = b.CommunityKey
group by    c.CommunityName

select      c.CommunityName, COUNT(*)
from        Community c with(NOLOCK)
join        CalendarEvent ce with(NOLOCK)
on          c.CommunityKey = ce.CommunityKey
where       ce.StartDateTime >= GETDATE()
group by    c.CommunityName

There are more tables, some that require additional joins... Can someone please help?

Answer 1

If I understand your question correctly, you are looking for community name along with the counts such as posts, blogs, event etc..

As your queries count individually, add dummy columns in the SELECT for the other counts and then in the end UNION them and get the SUM.

SELECT CommunityName , SUM(MessageCount), SUM(BlogCount), SUM(EventCount)
FROM (
    SELECT      c.CommunityName CommunityName , COUNT(*) MessageCount, 0 BlogCount, 0 EventCount
    FROM        Community c with(NOLOCK)
    JOIN        messages_ m with(NOLOCK)
    ON          c.ListKey = m.ListKey
    GROUP BY    c.CommunityName

    UNION

    SELECT      c.CommunityName, 0, COUNT(*), 0
    FROM        Community c with(NOLOCK)
    JOIN        Blog b with(NOLOCK)
    ON          c.CommunityKey = b.CommunityKey
    GROUP BY    c.CommunityName

    UNION

    SELECT      c.CommunityName, 0, 0, COUNT(*)
    FROM        Community c with(NOLOCK)
    JOIN        CalendarEvent ce with(NOLOCK)
    ON          c.CommunityKey = ce.CommunityKey
    WHERE       ce.StartDateTime >= GETDATE()
    GROUP BY    c.CommunityName
  ) CountsTable
GROUP BY CountsTable.CommunityName

CountsTable will look like

| COMMUNITYNAME | MESSAGECOUNT | BLOGCOUNT | EVENTCOUNT |
|---------------|--------------|-----------|------------|
|          Name |           10 |         0 |          0 |
|          Name |            0 |        20 |          0 |
|          Name |            0 |         0 |         30 |

So, you can GROUP BY name and sum up the counts to get your result

| COMMUNITYNAME | MESSAGECOUNT | BLOGCOUNT | EVENTCOUNT |
|---------------|--------------|-----------|------------|
|          Name |           10 |        20 |         30 |