Reputation: 875
I have a table like below:
In Postgresql I am trying to count user_id_2 based on combination of quiz_id and user_id_1. The question_id and questions_answered columns don't matter for the count. So for example, user_id_2 3320 should have a count of 3 because it corresponds to 3 rows of quiz_id 1234 and user_id_1 2220. On the other hand, user_id_2 8220 should only have a count of 1 because it only occurs once in quiz_id 1234 and user_id_1 2220. So the operation will be somewhat similar to "countif" function in Excel, except that in this case the count should take into account the quiz_id and user_id_1 columns as well.
The desired result should look like the table below, where a count_user_id_2 column is added containing those counts. The question_id column will be dropped in the output table, but questions_answered column must be preserved. As shown in the table below:
I've tried row_number() over()
function but it returned some excessively large number for each count. I've also tried SELECT quiz_id, user_id_1, user_id_2, count(*), questions_answered
but answer also didn't seem to be right. Any advice/suggestion will be greatly appreciated.
Upvotes: 0
Views: 771
Reputation: 5103
from the sample records, you provided you can try the below query
select quiz_id,user_id_1,user_id_2,count(*) as count_user_id_2,questions_answered
from table group by quiz_id,user_id_1,user_id_2,questions_answered;
Upvotes: 1