Reputation: 13555
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 :
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
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
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