CODEWITHSUNDEEP
sql
user3239173
user3239173

Reputation: 89

sql count statement leads to wrong value

select count(d.Games_played),count(d.No_ofgames) from 
(
 SELECT COUNT(UserGamePlayed.intID) AS 'Games_played',games.vchCompetency,b.No_Games as 'No_ofgames'
 FROM UserGamePlayed
 inner join games on games.intGameID=UserGamePlayed.intGameID
 inner join 
 (
 select COUNT(Games.intGameID) AS 'No_Games',vchCompetency,intGradeID from Games
 WHERE intGradeID=3
 GROUP BY vchCompetency,intGradeID
 ) as b on b.vchCompetency=games.vchCompetency
 WHERE intUserID=403 and UserGamePlayed.intGradeID=3
 GROUP BY games.vchCompetency,b.No_Games
)as d

the table which i get from d is:enter image description here

As per the table d i want to get a count of played,when exicute a full i am getting enter image description here

Upvotes: 0

Views: 41

Answers (1)

DrCopyPaste
DrCopyPaste

Reputation: 4117

You should replace COUNT with SUM (in your outer select only).

COUNT only counts (as the name indicates ;)) the rows while SUM will add up the values that are passed to it.

Upvotes: 1

Related Questions