SQL : SQL query to fetch the corresponding data

Question

So, the problem is that I need to find the the total number of gifts received by the people with the nationality 'America', Now I have 2 different tables named gift_info and users_info, the users_info table contains the nationality attribute while the gift_info contains the gift_id attribute.

I have come up with something like

SELECT users_info.user_id, gift_info.gift_id, users_info.nationality
FROM gift_info
LEFT JOIN users_info
ON  gift_info.gift_id = users_info.user_id
where users_info.nationality = 'America'

the output this query gives is the user_id, the gift_id and the nationalities 'America' while I need the the total number of gifts recived by people of nationality 'America'.

Is this condition ON gift_info.gift_id = users_info.user_id correct ? — Sujitmohanty30
– Sujitmohanty30, Commented Aug 23, 2020 at 22:17
Now that I think of it, it doesn't make sense, thanks for pointing it out! — Muhammad Ushay
– Muhammad Ushay, Commented Aug 23, 2020 at 22:49

Ross · Accepted Answer · 2020-08-23 22:37:15Z

1

Modify the SELECT statement to Count the number of gift IDs and add a GROUP BY clause:

SELECT COUNT(DISTINCT gift_info.gift_id)
FROM gift_info
LEFT JOIN users_info
ON  gift_info.user_id= users_info.user_id    
where users_info.nationality = 'America'
GROUP BY users_info.nationality = 'America'

Edited WHERE clause per @Sujitmohanty30's comment

edited Aug 23, 2020 at 22:37

answered Aug 23, 2020 at 22:18

Ross

464 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-08-24 01:05:47Z

0

If I understand correctly, I think you want:

SELECT COUNT(*)
FROM gift_info gi JOIN
     users_info ui
     ON gi.user_id = ui.user_id
WHERE ui.nationality = 'America';

Notes:

An outer join is not appropriate because you need the nationality.
Table aliases make the query easier to write and to read.
I think a simple COUNT(*) suffices to get the number of gives. If you want the number of people, use COUNT(DISTINCT ui.user_id).

answered Aug 24, 2020 at 1:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL : SQL query to fetch the corresponding data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related