SUM(subquery) in MYSQL

Question

Basically, I am trying the following:

SELECT m.col1, SUM(SELECT col5 FROM table WHERE col2 = m.col1)
FROM table AS m

This doesn't seem to work. Is there any solution?

Solution 1

Why don't you do this:

SELECT m.col1, (SELECT SUM(col5) FROM table WHERE col2 = m.col1)
FROM table AS m

Solution 2

yes - use joins

SELECT m.col1, SUM(j.col5) FROM table AS m 
       JOIN table AS j ON j.col2 = m.col1 GROUP BY m.col1

Solution 3

Sum is used inside the second select, where we want to sum the column. The col2 may be ambiguous column, if such column exists in the table m.

SELECT
    m.col1,
    (SELECT SUM(t.col5) FROM table AS t WHERE t.col2 = m.col1) AS sumcol
FROM table AS m