CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 691

MySQL count records and using a weight

I have a table in my database called "games" that records information of each game thats played (how many players, how long the game was, when the game was, etc).

Right now I query this information to simply just find how many games were played each day (i put the query I have below).

I am trying to modify so that it will "weight" each game based on how many players participated in the game (numPlayersEnd).

ie if a game has 2 players, it should count as 1.0 games. If a game has 3 players, it should count as 1.5 games. and if a game has 4 players it should count as 2.0 games.

Does anyone know how to do this in a SQL query? or will I have to do this with java after getting the query result?

SELECT 
DATE_FORMAT(datetimeStart, '%H') AS hour,
DATE_FORMAT(datetimeStart, '%d') AS day,
DATE_FORMAT(datetimeStart, '%m') AS month,
DATE_FORMAT(datetimeStart, '%Y') AS year, 
COUNT(*) AS count 

FROM 
games 

WHERE 
numPlayersEnd IS NOT NULL  AND datetimeStart >= (NOW() - INTERVAL 1 YEAR)

GROUP BY 
DATE_FORMAT(datetimeStart, '%Y-%m-%d')

Upvotes: 0

Views: 202

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

First, you are not grouping by the hour, so there is no reason to include that in the select. Actually, your aggregation columns in the select should be identical to the columns in the group by. But, that is not how you phrased the question.

Second, it seems like you just want to sum numPlayersEnd / 2. That is easy enough:

SELECT DATE_FORMAT(datetimeStart, '%d') AS day,
       DATE_FORMAT(datetimeStart, '%m') AS month,
       DATE_FORMAT(datetimeStart, '%Y') AS year, 
       COUNT(*) AS count ,
       SUM(numPlayersEnd / 2) as weighted_count
FROM games 
WHERE numPlayersEnd IS NOT NULL  AND datetimeStart >= (NOW() - INTERVAL 1 YEAR)
GROUP BY  DATE_FORMAT(datetimeStart, '%Y-%m-%d')

Upvotes: 1

Related Questions