sql setting and using variables in select statements

Question

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?

amow · Accepted Answer · 2015-04-16 09:40:33Z

1

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

answered Apr 16, 2015 at 9:40

amow

2,22313 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marc Rasmussen Over a year ago

Thank you for your answer. Can you explain to me why this cannot be used when creating views :(?

amow Over a year ago

@MarcRasmussen I think this will help.[[stackoverflow.com/a/5331792/3020810]]

Arion · Accepted Answer · 2015-04-16 09:41:21Z

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

Collectives™ on Stack Overflow

sql setting and using variables in select statements

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related