SUM(subquery) in MYSQL

mysql subquery aggregation
Dänu picture Dänu · Jan 6, 2012 · Viewed 52.5k times · Source

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?

Answer

Mithrandir picture Mithrandir · Jan 6, 2012

Why don't you do this:

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

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
Join vs. sub-query

I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query, and I hate it; I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. …

Read More
What is the error "Every derived table must have its own alias" in MySQL?

I am running this query on MySQL SELECT ID FROM ( SELECT ID, msisdn FROM ( SELECT * FROM TT2 ) ); and it is giving this error: Every derived table must have its own alias. What's causing this error?

Read More