开发者

finding the MAX value in a count

My code is

$query = "SELECT teamID, season,
    MAX(season) AS current_season,
    COUNT(DISTINCT matchID AND season=current_season) AS season_games_played,
    FROM finances
    WHERE teamID = '$开发者_高级运维_SESSION[teamID]'";

but this gives me an error :(

What I am trying to do is get the amount of games that the team has played this season, so current_season is to be the MAX value of column season.

My next line of code is to find out how many games the team played last season (MAX(season)-1) I assume, but how would this be written?


The most straightforward way of doing this would probably be

SELECT
     season,
       COUNT(DISTINCT matchID) AS season_games_played
    FROM finances
    WHERE teamID = '$_SESSION[teamID]' /*<-- Probable SQL injection risk!*/
    GROUP BY season
    ORDER BY season DESC
    LIMIT 2;

I have removed teamID from the SELECT list as you are already passing that in so know that already.

I assume '$_SESSION[teamID]' is php or something. Please read up on how to avoid SQL injection in that language.


SELECT
    teamID,
    season,
    COUNT(DISTINCT matchID) AS season_games_played,
FROM finances
WHERE teamID = ?
GROUP BY 1, 2
ORDER BY 2 DESC;

The first row will be the most recent season, the 2nd row will be the 2nd most recent season etc.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜