Mysql query for getting today's data in a sub-query #mysql-CodePudding

I have to get today's data in MySQL query.

SELECT 
    SUM(score) AS tscore,
    (SELECT 
            players.id_company
        FROM
            players
        WHERE
            id = 377) AS id_company,
    (SELECT 
            SUM(players_score.score),
                DATE_FORMAT(players_score.reg_dt, '%Y-%m-%d')
        FROM
            players_score
        WHERE
            id_player = 377
                AND DATE(reg_dt) = CURDATE())
FROM
    players_score
WHERE
    id_player = 377

In this query, it shows the error message that we can select one column which is understandable as I have used (col1, col2(for date)) as col. but I need a solution to get this done. thanks

CodePudding user response：

I don't know your schema or data, so there could very well be more efficient ways of gathering this information, like the date seems like it should be set as the current date if that's what you're pulling, instead of referencing the table rows for the date, but the following should work for you in regards to pulling the sums from different tables in the same query.

The problem with your query was that there are multiple things being asked and tallied in a single query, which won't work.

SELECT 
    (select SUM(score) AS tscore FROM players_score WHERE id_player = 377 AND DATE(reg_dt) = CURDATE()),
    (SELECT players.id_company FROM players WHERE id = 377) AS id_company,
    (SELECT SUM(players_score.score WHERE id_player = 377 AND DATE(reg_dt) = CURDATE()),
    (select DATE_FORMAT(players_score.reg_dt, '%Y-%m-%d') FROM players_score WHERE id_player = 377 AND DATE(reg_dt) = CURDATE())

CodePudding user response：

If you need today's data only, You should use the condition at the last only -

SELECT 
    SUM(score) AS tscore,
    (SELECT 
            players.id_company
        FROM
            players
        WHERE
            id = PS.id_player) AS id_company,  -- I have changed the 377 to players_score to make the query more dynamic.
    SUM(players_score.score),
    DATE_FORMAT(players_score.reg_dt, '%Y-%m-%d')
FROM
    players_score PS
WHERE
    id_player = 377
AND DATE(reg_dt) = CURDATE()