Search code examples
sqlgroup-byweighted

sql query to calculate weighted points

I have a database that stores forum posts with a grade of a, b, c. I want to assign 3 points for grade a, 2 points for grade b and 1 point for grade c and get the total points for all users. If the weight is even, I can use this query:

select count(*), userId from table_post group by userId;

But how to count weighted posts in one query? The fields are post_id, user_id, grade, post_content

Solution 
  • SELECT A.USER_ID,SUM(NEW_POINTS)
FROM
(
SELECT USER_ID,
       CASE WHEN grade = 'a'
            THEN points=3
            WHEN grade = 'b'
            THEN points=2
            WHEN grade = 'c'
            THEN points=1
            END AS new_points
FROM table_post
) A   
GROUP BY A.USER_ID;