Shani Gamrian Shani Gamrian - 5 months ago 11
SQL Question

SQL change a query to return results for all users

I use sql server and I have the tables:

tbl_GroupUser:
user_id, group_id


which is a table containing the users and the groups they are in (each group has more than one user_id and each user has more than one group).

tbl_group:
id, name

tbl_User:
id, age,....(information about the user)


There is a one to many relationship between id of tbl_Group to group_id and between id of tbl_User to user_id.

I created a query returning percentage of groups that has only 2 users in them for a specific user and I want a query that returns the results for all users. This is the query I made:

SELECT cast((SELECT count(*) FROM
(SELECT user_id FROM tbl_GroupUser WHERE group_id in
(SELECT group_id FROM (SELECT t1.group_id, count(distinct t1.user_id) as numberOfUsers
FROM tbl_GroupUser as t1 JOIN tbl_GroupUser as t2 ON t1.group_id = t2.group_id group by
t1.group_id) y WHERE y.numberOfUsers = 2)) as x WHERE x.user_id = 'user') as decimal) * 100 /
(SELECT count(*) FROM tbl_GroupUser WHERE user_id = 'user')


Which means, count all the groups the user is in that contains 2 users and divide it by the total number of groups the user is in.

An Example of what I want:

tbl_GroupUser:

group_id | user_id
------------------
1 | 1
1 | 2
2 | 1


The results:

user_id | p_Grp2
----------------
1 | 50.0
2 | 100.0

Answer

simple create User define function and use in select query

  alter function UDF_GetPercentage(@UserId int)
returns decimal(18,10)
as begin 

declare @Percentage decimal(18,10)
SELECT @Percentage = cast(
(SELECT count(*) FROM 
(SELECT user_id FROM tbl_GroupUser WHERE group_id in 
(SELECT group_id FROM (SELECT t1.group_id, count(distinct t1.user_id) as numberOfUsers 
FROM tbl_GroupUser as t1 JOIN tbl_GroupUser as t2 ON t1.group_id = t2.group_id group by
t1.group_id) y 
WHERE y.numberOfUsers = 2)
)
as x

WHERE x.user_id in (@UserId)
) as decimal)
* 100 
/
(SELECT count(*) FROM tbl_GroupUser WHERE user_id in (@UserId ))

return @Percentage

end

and use in select query like this

SELECT   user_id, dbo.UDF_GetPercentage(user_id) as p_Grp2
FROM     tbl_GroupUser
group by user_id