CODEWITHSUNDEEP
mysqlsql
Marc Rasmussen
Marc Rasmussen

Reputation: 20545

sql setting and using variables in select statements

Say for instance you have the following sql:

SELECT 
    attempt.user_id,
    attempt.team_id,
    SUM(CASE
        WHEN attempt.module_type_id IN (3 , 4, 5, 6) THEN attempt.score
    END) / COUNT(CASE
        WHEN attempt.module_type_id IN (3 , 4, 5, 6) THEN 1
    END) as avg_score,
    (SELECT 
            COUNT(atha.academy_module_id)
        FROM
            academy_team_has_academy_module atha
        WHERE
            atha.academy_team_id = attempt.team_id
    ) as num_modules

FROM
    academy_attempt attempt
GROUP BY attempt.user_id

Now you wish to use num_modules to do the following action:

    num_modules / COUNT(attempt.module_id) as completed

When i run this i get (as expected) Unknown column num_modules

So is there any way i can set it as a variable so i can use the value?

Upvotes: 0

Views: 49

Answers (2)

Arion
Arion

Reputation: 31239

I think the way to do this is to do a sub query like this:

SELECT
    tbl.user_id,
    tbl.team_id,
    tbl.num_modules / tbl.countModule_id as completed,
    tbl.avg_score,
    tbl.num_modules
FROM
(
    SELECT 
        attempt.user_id,
        attempt.team_id,
        SUM(CASE
            WHEN attempt.module_type_id IN (3 , 4, 5, 6) THEN attempt.score
        END) / COUNT(CASE
            WHEN attempt.module_type_id IN (3 , 4, 5, 6) THEN 1
        END) as avg_score,
        (SELECT 
                COUNT(atha.academy_module_id)
            FROM
                academy_team_has_academy_module atha
            WHERE
                atha.academy_team_id = attempt.team_id
        ) as num_modules,
        COUNT(attempt.module_id) as countModule_id
    FROM
        academy_attempt attempt
    GROUP BY attempt.user_id
) as tbl

Upvotes: 1

amow
amow

Reputation: 2223

You can change the 

(SELECT 
        COUNT(atha.academy_module_id)
    FROM
        academy_team_has_academy_module atha
    WHERE
        atha.academy_team_id = attempt.team_id
) as num_modules

to 

@num_modules:=(SELECT 
        COUNT(atha.academy_module_id)
    FROM
        academy_team_has_academy_module atha
    WHERE
        atha.academy_team_id = attempt.team_id
) as num_modules

and then you can use the @num_modules afterwords as a varible.

Just like @num_modules / COUNT(attempt.module_id) as completed

Upvotes: 1

Related Questions