MySQL - Cannot reference result of a subquery

Question

Hit a roadblock and hoping someone here is able to help please?

edit: DB<>FIDDLE : https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=c73f8ec9a60f530fe4ad489dc743f9b9

I have 3 tables:

marks - which uses grades;
target - which also uses grades;
grades - a lookup for grades to points.

What I am trying to do is calculate the total points for grades within the marks table, then calculate the total target points by multiplying the points value for the target by the number of grades within the marks table for a given person (adno).

I'm able to sum and count the points values from the marks table without a problem, but as I've used an inner joint for the marks to grades already I cannot add a further one for target to grades so I've used a subquery.

However when I try to use the result of subquery (EDIT single_target_points , not target_points as I originally posted) in the calculation in the line straight after it I get the error :

[Err] 1054 - Unknown column 'single_target_points' in 'field list'

This is the query I am trying:

SELECT
       marks.adno,
       Sum(grades.points) AS total_points,
       Count(grades.points) AS no_of_subjects,
       (SELECT grades.points FROM targets INNER JOIN grades ON targets.grade = grades.grade WHERE targets.adno = marks.adno GROUP BY grades.points) AS single_target_points,
    single_target_points*no_of_subjects AS target_points
FROM
    marks
INNER JOIN grades ON marks.resultvalue = grades.grade
INNER JOIN targets ON targets.adno = marks.adno
GROUP BY
    marks.adno

It's a mistake to think like this 'when I try to use the result of subquery (target_points) in the calculation in the line straight after it I get the error' , target_points is not available further down the select.. — P.Salmon
– P.Salmon, Commented Dec 7, 2021 at 12:49
Please post table description some data and expected result to give a better idea. Use dbfiddle.uk/?rdbms=mysql_8.0 — Ergest Basha
– Ergest Basha, Commented Dec 7, 2021 at 13:09
Does this answer your question? MySQL: Referencing subquery columns — Stu
– Stu, Commented Dec 7, 2021 at 13:22

DRapp · Accepted Answer · 2021-12-08 13:06:27Z

0

This will resolve the query for you, but not sure what you are doing is completely accurate. I took your in-line query and made it a subquery including the "adno" (person id) to get all distinct points. Using that and JOINING based on the person like the others.

SELECT
        m.adno,
        Sum(g.points) AS total_points,
        Count(g.points) AS no_of_subjects,
        SUM( TP.single_target_points * g.points) AS target_points
    FROM
        marks m
            JOIN grades g
                ON m.resultvalue = g.grade
            JOIN students s
                ON m.adno = s.adno
            JOIN targets t
                ON m.adno = t.adno
                JOIN
                ( SELECT distinct
                        t2.adno,
                        t2.grade,
                        g2.points single_target_points 
                    FROM 
                        targets t2 
                            JOIN grades g2
                                ON t2.grade = g2.grade ) TP
                    on t.adno = TP.adno
                    and t.grade = TP.grade
    GROUP BY 
        m.adno

Now, that being said, it looks like you are trying to compute a person's GPA (grade point average). If you can EDIT your existing post and provide samples of data (use spaces to align, not tabs) even if fictional names (but not necessary since things here are all ID values and otherwise not private). It would help to see the basis of what computations are going on. If not done correctly, you will get Cartesian results and skew your points due to multiple entries * multiple entries = oversized expected answer values.

Also, I Updated your dbfiddle. You inserted into adno instead of students the sample records, inserted into targets the grades, so those tables were blank. I had to correct that. Then changed grades to integer since doing math (via sum( g.points)). Best to use proper data types vs everything as character.

At least the queries are now working, but does not make sense if this is GPA calculations -- as far as I know and have done for U.S. college transcript purposes.

edited Dec 8, 2021 at 13:06

answered Dec 7, 2021 at 13:08

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

4 Comments

Ian Gale Over a year ago

Thank you for this, I'll try it shortly. I've made a dbfiddle here: dbfiddle.uk/…

DRapp Over a year ago

@IanGale, no Students table defined in dbfiddle

Ian Gale Over a year ago

Sorry, I removed it for simplicity for the main question, this fiddle has that table: dbfiddle.uk/…

DRapp Over a year ago

@IanGale, your grades table you have POINTS as CHARACTER. If you are trying to perform math by multiplying, should they be numeric/integer type field instead? I see you are doing count which should not care, just trying to assess best data type for purpose.

Collectives™ on Stack Overflow

MySQL - Cannot reference result of a subquery

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related