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