CODEWITHSUNDEEP
mysqlsqljoingroup-by
Nouphal.M
Nouphal.M

Reputation: 6344

Mysql SUM based on condition

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.

Upvotes: 1

Views: 92

Answers (2)

Hasaan Mubasher
Hasaan Mubasher

Reputation: 160

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

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35623

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
;

Upvotes: 2

Related Questions