CODEWITHSUNDEEP
sqljoingroup-by
Skiminock
Skiminock

Reputation: 149

Join Three Tables And Group By One Field With COUNT function

I have three tables - Sections, Themes and Comments.

Simple View:

Sections

-SectionId

Themes

-ThemeId -SectionId

Comments

-CommentId -ThemeId

And I need to write sql query to get the result like this:

-SectionId -CountOfThemes -CountOfComments

Resolved:

Select 
    Section.SectionId As SessionId, 
    Section.SectionTitle As SessionTitle,
    Count(Distinct Theme.ThemeId) As CountTheme,
    Count(Distinct Comment.CommentId) As CountComment

From Section
Left Join Theme On Section.SectionId = Theme.SectionId
LEFT JOIN Comment ON Theme.ThemeId = Comment.ThemeId
Group By 
    Section.SectionId,
    Section.SectionTitle

Upvotes: 1

Views: 453

Answers (1)

K Richard
K Richard

Reputation: 1984

Try using COUNT(DISTINCT Theme.ThemeId) and COUNT(DISTINCT Comment.CommentId)

I think your issue is that you have a one to many relationship between themes and comments, and therefore you are getting duplicate ThemeIds counted where there is more than one comment.

Also your code: left Join Comment On Comment.ThemeId = Theme.ThemeId

I would consider changing to LEFT JOIN Comment ON Theme.ThemeId = Comment.ThemeId

Upvotes: 1

Related Questions