CODEWITHSUNDEEP
mysqlsql
Bisoux
Bisoux

Reputation: 522

Conditional statement in mysql

I have the following query,

SET @theYear = 2017;
SET @theTeam = null;

SELECT 
    SUM(growth), r.theYear
FROM
    Reports r
WHERE
        r.theYear = @theYear
        AND (@theTeam IS NULL
        OR r.team = @theTeam)
GROUP BY theYear
ORDER BY theYear;

The above works fine if a team is mentioned or is null. What I want to do now is include all teams should the variable @theTeam be null.

I am trying something like the following,

SELECT 
    IF(@theTeam,
        (SELECT 
                SUM(growth), r.theYear
            FROM
                Reports r
            WHERE
                r.theYear = @theYear
                AND r.team = @theTeam
            GROUP BY theYear
            ORDER BY theYear),
        (SELECT 
                SUM(growth), r.theYear
            FROM
                Reports r
            WHERE
             r.theYear = @theYear
            GROUP BY theYear
            ORDER BY theYear));

It fails with Error Code: 1241. Operand should contain 1 column(s) I am using MYSQL 8.0.17 Would there be a another way to do this?

Upvotes: 0

Views: 62

Answers (1)

Barmar
Barmar

Reputation: 780724

Try this:

SELECT 
    SUM(growth), r.theYear
FROM
    Reports r
WHERE
        r.theYear = @theYear
        AND r.team <=> IFNULL(@theTeam, r.team)
GROUP BY theYear
ORDER BY theYear;

When @theTeam is null it will compare r.team with itself, which is always true.

It also uses the <=> null-safe comparison operator, which will work when r.team is null.

Upvotes: 3

Related Questions