question

MOHITJAIN-0808 avatar image
0 Votes"
MOHITJAIN-0808 asked MOHITJAIN-0808 commented

SQL JOIN related query

Hi, Looking for your help on the SQL JOIN, basically we want to List down the first_names with total number of posts and total number of likes in a single table of below tables in the screenshot? I've tried couple of joins and methods but unable to come to a single table with accurate number of count, especially because  post#8 were liked by Alex and Frank both and when i use GROUP by, it always show the wrong count.



Tables

Expected Result


sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered MOHITJAIN-0808 commented

So I replace Number_of_posts with Number_of_likes in the second subquery (which technically is known as a derived table), I seem to get the expected result:

SELECT pn.First_name, isnull(ps.Number_of_posts, 0) AS Number_of_posts,
           isnull(l.Number_of_likes, 0) AS Number_of_likes
    FROM   person pn
    LEFT   JOIN   (SELECT person_id, COUNT(*) AS Number_of_posts
                   FROM   posts
                   GROUP  BY person_id) AS ps ON ps.person_id = pn.person_id
    LEFT    JOIN   (SELECT person_id, COUNT(*) AS Number_of_posts
                    FROM   likes
                    GROUP  BY person_id) AS l ON l.person_id = pn.person_id

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Awesome, thank you!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered MOHITJAIN-0808 commented
SELECT pn.First_name, isnull(ps.Number_of_posts, 0) AS Number_of_posts,
       isnull(l.Number_of_likes, 0) AS Number_of_likes
FROM   person pn
LEFT   JOIN   (SELECT person_id, COUNT(*) AS Number_of_posts
               FROM   posts
               GROUP  BY person_id) AS ps ON ps.person_id = pn.person_id
LEFT    JOIN   (SELECT person_id, COUNT(*) AS Number_of_posts
                FROM   likes
                GROUP  BY person_id) AS l ON l.person_id = pn.person_id


Note: this is untested solution. Had you posted your data as CREATE TABLE + INSERT statements, I would have tested the solution.

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sure, here it is:

https://www.codepile.net/pile/Gv85x9D6

(please scroll to view all)

Thanks,
Mohit

0 Votes 0 ·

"This pile has been deleted".

It would be simpler to just include the statements in the post.

Then again, did you try my query?

0 Votes 0 ·

Hi, thanks for your response!

It exceeded 1000 character limit, hence posted the cmds elsewhere.

And yes i tried executing the query but it gives column doesn't exists!

82151-screenshot-2021-03-28-at-50240-pm.png


0 Votes 0 ·
Show more comments
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @MOHITJAIN-0808

Welcome to Microsoft Q&A!

You could refer Erland's query which you could get expected output.

Please also refer below which replace the 'count(...) group by...' with 'count(...) over (partition by ...)':

 SELECT distinct a.first_name
 ,isnull(b.Number_of_posts,0) Number_of_posts
 ,isnull(c.Number_of_likes,0) Number_of_likes  
 FROM person a
 left join 
 (SELECT  person_id, COUNT(post_id) over (partition by person_id) AS Number_of_posts
 FROM   Post) b on a.person_id=b.person_id
 left join 
 (SELECT person_id, COUNT(likes_id) over (partition by person_id) AS Number_of_likes
 FROM   likes) c on a.person_id=c.person_id

Output:

 first_name    Number_of_posts    Number_of_likes
 Alex    1    1
 Bob    2    0
 Frank    1    1
 John    2    1
 Julie    2    1

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.