mercredi 15 juin 2016

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

Aucun commentaire:

Enregistrer un commentaire