SUM(subquery) in MYSQL

问题:

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?

回答1:

Why don't you do this:

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

回答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

回答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