optimize Select sub query using alias

Question

i have a query that selects multiple values from the same table in sub query

SELECT ...,
(
    SELECT IFNULL(SUM(pts), 0)
    FROM grades 
    WHERE s.id = student 
) AS spts,
(
    SELECT IFNULL(SUM(mat), 0)
    FROM grades 
    WHERE s.id = student
) AS smat,
(
    SELECT IFNULL(SUM(bio), 0)
    FROM grades 
    WHERE s.id = student
)AS sbio
FROM ...

its working perfectly now but i think it can be optimized into one sub query instead of 3.

what i tried

SELECT ...,
(
    SELECT IFNULL(SUM(pts), 0) AS spts, IFNULL(SUM(mat), 0) AS smat, IFNULL(SUM(bio), 0) AS sbio
    FROM grades
    WHERE s.id = student
)
FROM ...

but i keep getting the following error

"message":SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

expected output

{
"spts": "89",
"smat": "44",
"sbio": "45"
}

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-09-05T00:16:41

Simplify it using a left join:

SELECT ...,
       COALESCE(spts, 0) as spts,
       COALESCE(smat, 0) as smat,
       COALESCE(sbio, 0) as sbio
FROM ... LEFT JOIN
     (SELECT student, SUM(pts) AS spts, SUM(mat) AS smat, SUM(bio) AS sbio
      FROM grades
      GROUP BY student
     ) g
     ON s.id = g.student;

Depending on what else is in the FROM clause (and potentially WHERE clause), this may not speed up the query.

optimize Select sub query using alias

1 Answers