CODEWITHSUNDEEP
sqlpostgresqlgroup-by
DB at PS
DB at PS

Reputation: 111

SQL - Get count of group by column but also select top item of group

I am trying to figure out the proper sql to accomplish something semi complex. I am using postgres in this particular scenario, but a sql statement that could work for other platforms would be appreciated as well.

I have tables called users and responses. A user will complete activities and for each attempt at an activity we are tracking a response by the user and whether that response resulted in a passing score for the activity.

Relevant tables and columns:

Users: id (int), username (varchar), classroom_id (int), type (varchar)

Responses: id (int), activity_id (int), user_id (int), is_passing_score (bool)

Activities: id (int), type (varchar), name (varchar)

What I would like to be able to pull from these tables is the activity_id, number of attempts for each activity, and whether the activity was passed. 

select activity_id, count(activity_id)
from (
  select *
  from responses R
  inner join users U on U.id = R.user_id
  where U.classroom_id = '114' AND U.type = 'Student'
  group by R.activity_id, R.id, U.id
  order by activity_id, is_passing_score DESC
) as foo
group by activity_id
order by activity_id

The above query will get me the activity_id and count of attempts, however I'm struggling to also get whether any of the responses resulted in a passed activity (via the is_passing_score column). This would be true if any of the responses had the is_passing_score set to true.

I also tried something like this which I think could get me the true/false value that I'm seeking for whether or not the activity was passed.

select rn, * 
from (
  select row_number() OVER(PARTITION BY activity_id ORDER BY is_passing_score DESC) AS rn, *
  from responses R
  inner join users U on U.id = R.user_id
  where U.classroom_id = '114' AND U.type = 'Student'
  group by R.activity_id, R.id, U.id
  order by activity_id, is_passing_score DESC
) as foo
WHERE rn = '1'
order by activity_id

I think that query will order the subquery by the is_passing_score and then grab just the top one, which if its true would be that, else if its false just whatever. My big problem, if that second query is in fact correct, is getting both the count and that true/false value. I feel like I'm close it is just a matter of being able to do the row select and also get a count in one query.

I want my end result to be a list of activity_id, user_id, number of attempts, and was it passed.

Upvotes: 0

Views: 290

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I think you want something like this:

SELECT r.activity_id, count(*) AS num_attempts,
       CAST(MAX(CAST(is_passing_score AS INT)) AS BOOL) AS has_passed
FROM responses r INNER JOIN
     users u
     ON u.id = r.user_id
WHERE u.classroom_id = 114 AND u.type = 'Student'
GROUP BY r.activity_id;

Upvotes: 2

Related Questions