sql count(*) query on results of another query performing multiple results

Question

how to perform sql count(*) query on results of another query performing multiple results ?

I've 3 tables :--

user_subscribe table where I've 'user_id' and other columns.
user table where we have 'user_id' & 'country_id' columns where user_id is being linked with user_subscribe table and country_id is being linked with 'country' table.
1. country table having 'country_id' column.

I need to get total count of user_subscribe based on users (user_id) who belongs to same country. A Help is highly appreciated as I'm being stuck on this problem from last 7 days.

**sql count() query on results of another query returning multiple results — Sumit
– Sumit, Commented May 8, 2018 at 5:34

Chintan7027 · Accepted Answer · 2018-05-08 05:53:14Z

2

SELECT COUNT(US.user_id) as country_user_subscribed
FROM user_subscribe as US
LEFT JOIN users as U ON user.id=US.user_id
LEFT JOIN countries as C on C.id=U.country_id
GROUP BY C.country_id

answered May 8, 2018 at 5:53

Chintan7027

7,6498 gold badges40 silver badges51 bronze badges

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

Comments

Kaval Patel · Accepted Answer · 2018-05-08 06:51:40Z

1

Try This Solution :

SELECT C.CountryName,Count(u.UserId) AS UserCount FROM  User_Subscribe AS us 
LEFT JOIN User AS u ON us.UserId = u.UserId
LEFT JOIN Country AS c ON u.CountryId = c.CountryId
WHERE c.CountryId = 3
GROUP BY c.CountryName

edited May 8, 2018 at 6:51

answered May 8, 2018 at 6:00

Kaval Patel

6664 silver badges20 bronze badges

4 Comments

Kaval Patel Over a year ago

add where condition for that i am updating my query

Sumit Over a year ago

Hi Kavel thanks alot for responding, Your Query is working properly without any problem. However, the issue is still stuck as I need to find result based on the country_id which I already have. For Example , India has country_id = 3 So I need to find out total subscriber from 'user_subscribe' table based on 'user_id' which belongs to country_id=3 (India).

Kaval Patel Over a year ago

please check query now and if it work then please accept and vote answer so it also helps other

Sumit Over a year ago

Thanks Kavel it is working perfect for us, However I don't really think that 'group by' is necessary as per our requirement. Cheers to you anyways. :)

Collectives™ on Stack Overflow

sql count(*) query on results of another query performing multiple results

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related