Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
616 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

Please log in or register to reply this article.

OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...