I have 2 tables.
1st table: duels
| duelId | user1Id | user2Id | gameId | winnerId |
2nd table: usergameprogress
| usergameprogressId | userId | gameId | gameStar |
Given an userId
, I would like to get duel count, gameStar, win count for each gameId.
Example return:
| duelCount | duelWinCount | gameStar | gameId |
I have managed to get duelCount, gameStar and gameId given a userId
but I couldn't add duelWinCount
to my query result. How do I do that ?
My query:
SELECT
COUNT(d1.duelId) AS duelCount,
usergameprogress.gameId, usergameprogress.gameStar
FROM
duels d1
JOIN
usergameprogress ON (usergameprogress.gameId = d1.gameId)
WHERE
d1.user1Id = "gkfurcwsi033qzxg0u2bmj1ekebsklej"
OR d1.user2Id = "gkfurcwsi033qzxg0u2bmj1ekebsklej"
GROUP BY
usergameprogress.gameId
EDIT:
solved thanks to comment use sum instead of count
SELECT sum(case when d1.user1Id = 'gkfurcwsi033qzxg0u2bmj1ekebsklej' OR d1.user2Id="gkfurcwsi033qzxg0u2bmj1ekebsklej" then 1 else 0 end) AS totalDuelCount,sum(case when winnerId="gkfurcwsi033qzxg0u2bmj1ekebsklej" then 1 else 0 end) AS duelWinCount,usergameprogress.gameId,usergameprogress.gameStar FROM duels d1 JOIN usergameprogress ON (usergameprogress.gameId = d1.gameId) GROUP BY usergameprogress.gameId
question from:
https://stackoverflow.com/questions/65907864/how-do-i-add-another-count-statement-with-different-condition-to-sql-query 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…