mardi 14 juin 2016

SQL change a query from a specific user to all users

I use sql server and I have the tables:

tbl_Message:
id, user_id, group_id

Which is a table containing all the message with the details of who wrote the message (user_id) and in which group it was sent (group_id)

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).

I created a query that return the ratio between the messages the user sent and the the messages he received. I made a query that works for a specific user and I want a query that shows the results for all the user exists. This is the query I made:

SELECT CAST((SELECT COUNT(*) FROM tbl_Message WHERE user_id = ‘user’)
as decimal) / 
(SELECT COUNT(*) FROM tbl_Message WHERE group_id in 
(SELECT group_id FROM tbl_GroupUser WHERE user_id = ‘user’) and user_id != ‘user’)

Example:

tbl_GroupUser

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

tbl_Message

id | user_id | group_id
-----------------------
1  | 1       | 1
2  | 2       | 1
3  | 2       | 1

Will return:

user_id | ratio
---------------
1       | 1/2
2       | 2/1

Aucun commentaire:

Enregistrer un commentaire