0

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

Purres
  • 1,271
  • 2
  • 11
  • 12

1 Answers1

0
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;
Teja
  • 13,214
  • 36
  • 93
  • 155
  • Good query. I got error then using 'points' since the column doesn't exist. Assign a number right after 'then' works. – Purres Oct 22 '13 at 18:44