mysql - How do i add another COUNT statement with different condition to sql query

Question

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

Answer 1

You should try to make 1 or 2 subqueries