CODEWITHSUNDEEP
sql
deltanovember
deltanovember

Reputation: 44041

How do I perform the following SQL?

My database schema is as follows:

match_id, user_id, rating

Matches come in pairs e.g.

7, 2947, 5
7, 273, 4

In the above example for match 7, user 2947 was rated 5 by user 273. User 273 was rated 4 by user 2947. How can I perform a query that produces the following output

rater_id,rating,num_ratings

Which lists raters, their associated ratings and the number of ratings. So for example if user 2947 gave ten 4's and eleven 5's I would like to see:

2947,4,10
2947,5,11

Upvotes: 0

Views: 42

Answers (3)

varadarajan
varadarajan

Reputation: 514

AS said by Somid3 , it is two column grouping i think. try this one.

CREATE TABLE #match(match_id int,user_id int ,rating int)
SELECT m1.user_id,m1.rating, COUNT(m1.Rating)
FROM 
#match m1
Group by m1.user_id,m1.rating

Upvotes: 0

somid3
somid3

Reputation: 680

Looks like a two column group by. Look at this tutorial

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Join the table to itself:

SELECT r1.user_id, r2.rating, count(*)
FROM Ratings r1
INNER JOIN Ratings r2 ON r2.match_id=r1.match_id AND r2.user_id<>r1.user_id
GROUP BY r1.user_id, r2.rating

Upvotes: 4

Related Questions