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?

Constrictor

I had the same issue, so I solved by add extra parentheses to the subquery inside SUM. – Gyratory

Answer 1

Why don't you do this:

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

Multifoil

Answer 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

Bromoform

Answer 3

Another solution is to enclose the subquery in additional parentheses

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

In my case this solved it.

Also the MySQL documentation (13.2.15 Subqueries) says:

A subquery must always appear within parentheses.

Noontime

Answer 4

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

Polder