user782104
user782104

Reputation: 13555

How to count the result in MYSQL in this scenario?

The table structure is like that:

planID TinyInt(1)
userID INT(11)
isComplete TinyInt(1)
isQuit TinyInt(1)

So, first of all, I need to :

  1. get the result from a specific userID
  2. for each user, they may have join plan 1 (e.g. planID = 1), plan 2 or plan3 (can join again, unlimited time)
  3. Count the isComplete times and isQuit times for all plan (1, 2, 3)

So, a user with ID 1 , may join plan 3 3 times, and 1 complete 2 quit, join plan 2 1 times , and 2 complete etc....

Somehow attempt

SELECT COUNT(`isComplete`) as total_complete, COUNT(`isQuit`) as total_quit FROM `user_plan` WHERE `userID` = 1 AND isComplete = 1 OR isQuit = 1 GROUP BY `planID` 

but not the same as expected

If it is too complex I can query 3 times for each plan, Thanks for helping

Updated Sample Input:

planID      | userID    | isComplete    | isQuit
-------------------------------------------------------
1           | 1         | 0             | 1
1           | 1         | 1             | 0
1           | 1         | 0             | 0

Upvotes: 0

Views: 54

Answers (2)

user782104
user782104

Reputation: 13555

This is the query inspire by the Jithin Shaji 's answer, thanks for helping again.

SELECT  SUM(isComplete) as total_complete,
        SUM(isQuit) as total_quit,
        planID
FROM    user_plan
WHERE   userID = 31 
GROUP   BY planID

Upvotes: 0

Jithin Shaji
Jithin Shaji

Reputation: 6073

If the case was in SQL Server, it wold have been like this. (If is read the question correct)

SELECT  userID,
        SUM(isComplete) as total_complete, 
        SUM(isQuit) as total_quit 
FROM    user_plan
WHERE   userID = 1 
AND     isComplete = 1 
GROUP   BY userID

Upvotes: 2

Related Questions