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
Aucun commentaire:
Enregistrer un commentaire