Comments by Necron8 (Top 10 by date)

For the first query, I had to name the select as a different table name since I had to retrieve each and every user prediction won / lost, so one is solved. I have commented regarding the user stats above but couldn't get it to work. Can you help me and see the above comment? Thanks!

Hi there guys, solved the first query:

SELECT u.userID, u.userName, u.userImageUrl, l.leagueName ,
COUNT(g.gameId) AS predTotal,
(SELECT COUNT(gw.gameId) FROM games AS gw WHERE gw.gameResultOut = 1 AND gw.gameWon = 1) AND gw.gameUserID = u.user_id AS gamesWon,
(SELECT COUNT(gl.gameId) FROM games AS gl WHERE gl.gameResultOut = 1 AND gl.gameWon = 0) AND gl.gameUserID = u.user_id AS gamesLost,
ub.balance
FROM games AS g
LEFT JOIN league AS l ON l.leagueId = g.gameLeagueId
LEFT JOIN user AS u ON u.user_id = g.gameUserID
LEFT JOIN user_balance AS ub ON ub.userId = u.userID
WHERE l.leagueId = 4
GROUP BY u.userId
ORDER BY ub.balance DESC

I forgot to change the names of the select to get the unique user predictions won/lost count.

As regarding to statistics, I'm trying to get the following result but I'm getting gamesWon column undefined:

Trying to get a ratio for the number of games won per user:

SELECT
g.gameName,
(SELECT COUNT(gw.gameId) FROM games gw WHERE g.gameResultOut = 1 AND g.gameWon = 1 AND gw.gameUserID = u.user_id) AS gamesWon,
COUNT(g.gameId) AS gamesPlayed,
(gamesWon / gamesPlayed) AS winRatio
FROM
games g
INNER JOIN user u ON u.user_id = g.gameUserID
GROUP BY g.gameId, u.user_id
ORDER BY winRatio DESC
LIMIT 1

I can't seem to make it work ;/

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'Total Class Hours'), min(sa.studentName) as 'studentName', SUM(CASE WHEN sa.' at line 1

SELECT SUM(a.att_duration as 'TotalClassHours'), min(sa.studentName) as 'studentName', SUM(CASE WHEN sa.sa_type = 'P' THEN att_duration end) AS 'Total Hours Attended' FROM attendance AS a LEFT OUTER JOIN student_attendance AS sa ON sa.attendanceID = a.attID LEFT OUTER JOIN periods AS p ON p.perioID = a.attID LEFT OUTER JOIN students AS s on s.studentID = sa.studentID WHERE NOT sa.sa_type = 'N/A' AND a.att_courseID = '1' AND a.att_periodID = '8' GROUP BY sa.studentID



Do I need to change some of the tables in this query or? everything seems fine. I tried changing a.att_duration to attendance.attendance_duration and all the others but the same error occurs. What should I do? Sry for being such a noob at this ;/

Currently, what I did was to make a while loop to get the Period Start Date and Period End date and also the dates between. From the while loop, I search the attendance and get the students associated to that attendance.

If I have 3 attendances on a certain date (ex 26/2/2012), the query gives you 3 sets of students.
What I want is one set of student with the lesson start/end and attendance as I described above.

How could this be done?