Mysql SUM based on condition

Question

I need help to formulate a query to calculate points based on user action. There is a user table which stores user info and each user belongs to a specific category (User category). Each category has different levels (say level1, level2 etc..) and each level has a set of tasks(Task1,Task2...) to be completed. Each task can be performed multiple times. But points is calculated based on a maximum limit field.

Task 1 - 10 points ( Max:2 - Means user can perform this task N times but for point calculation it will be only counted twice )

Task 2 - 5 points ( Max:1 )

For example lets say User1 performs Task1 5 times, but for point calculation Task1 will only be counted 2 times , so total points is 20. This is similar for all tasks.

On completing each task the user gains N points and get upgraded to next level. Please find below the table structure and query:

users

uid , uname , uc_id ( References User Category ) , ul_id (References user level and indicates current level of user )

user_category

uc_id ....

user_level

ul_id, uc_id (References User Category) ...

level_tasks

lt_id , ul_id (References User level) , lt_point, lt_max

user_tasks

ut_id, lt_id (References level task), uid (References user)

The below is the query i have tried so far :

SELECT uid, SUM(LT.lt_point/LT.lt_max) as TotalLevelPoints FROM users AS U INNER JOIN user_tasks AS UT ON UT.ut_user_id = U.id INNER JOIN level_tasks AS LT ON LT.lt_id = UT.lt_id INNER JOIN user_level AS UL ON UL.ul_id = LT.ul_id INNER JOIN user_category AS UC ON UC.uc_id = UL.uc_id WHERE UT.ut_status = 1 AND U.uid = 1 AND UL.ul_id = 1 GROUP BY U.uid

This gives all the sum without considering the max limit, how should i write so that sum is calculated on that column also. Hope my question is clear enough.. Any help would be really appreciated.

Paul Maxwell · Accepted Answer · 2017-12-07 07:07:15Z

2

I believe you will need to do this in 2 steps. First sum the points and compare to the maximums allowed for each task type. Once that is determined, sum those results for each user. e.g.

SELECT
      u.uid
    , SUM(user_task_points) AS user_task_points
FROM users AS u
INNER JOIN (
      SELECT
            ut.UT_user_id
          , lt_id
          , lt.LT_point
          , lt.LT_max_times
          , CASE WHEN SUM(lt.LT_point) > (lt.LT_point * lt.LT_max_times) THEN (lt.LT_point * lt.LT_max_times) 
                 ELSE SUM(lt.LT_point)
            END AS user_task_points
      FROM user_tasks AS ut
      INNER JOIN level_tasks AS lt ON ut.UT_rule_id = lt.LT_id
      INNER JOIN  user_level AS UL ON UL.ul_id = LT.ul_id
      WHERE ut.UT_status = 1
          AND ut.UT_user_id = 1
          AND ul.ul_id = 1
      GROUP BY
            ut.UT_user_id
          , lt_id
          , lt.LT_point
          , lt.LT_max_times
      ) AS p ON u.id = p.UT_user_id
GROUP BY
      u.uid
;

edited Dec 7, 2017 at 7:07

answered Dec 7, 2017 at 6:55

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Paul Maxwell Over a year ago

sorry, there were quite a few edits, please ensure you refresh

Nouphal.M Over a year ago

Thanks man, you save my day. SQL is amazing. But i have a small doubt what is the logic in grouping records based on points and max times?

Nouphal.M Over a year ago

here : GROUP BY ut.UT_user_id , lt_id , lt.LT_point , lt.LT_max_times

Paul Maxwell Over a year ago

The logic for the first level of grouping is to allow comparison of summed scores to the allowed maximum.

Hasaan Mubasher · Accepted Answer · 2017-12-07 07:13:44Z

0

There is some discrepancy between column names in schema and query you have provided. I have gone with schema.

Query is not tested. But this approach should work.

SELECT uid, SUM(LevelPoints) TotalLevelPoints
FROM ( SELECT U.uid, LT.lt_id, SUM((CASE WHEN COUNT(*) > lt_max THEN lt_max ELSE COUNT(*) END)*lt_point) LevelPoints
       FROM users U
       INNER JOIN user_tasks UT
       ON U.uid = UT.uid
       INNER JOIN level_tasks LT
       ON UT.lt_id = LT.lt_id
       GROUP BY U.uid, LT.lt_id
      )
GROUP BY uid

answered Dec 7, 2017 at 7:13

Hasaan Mubasher

1601 silver badge6 bronze badges

Collectives™ on Stack Overflow

Mysql SUM based on condition

users

user_category

user_level

level_tasks

user_tasks

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

users

user_category

user_level

level_tasks

user_tasks

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related