Menu
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?
905
A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the SUM, COUNT, MIN, or MAX function, but you do not want the aggregate function to apply to the main query.
A subquery in MySQL is a query, which is nested into another SQL query and embedded with SELECT, INSERT, UPDATE or DELETE statement along with the various operators. We can also nest the subquery with another subquery.
Summary: in this tutorial, we will show you how to use the MySQL subquery to write complex queries and explain the correlated subquery concept. A MySQL subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. Also, a subquery can be nested within another subquery.
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.
If we use a subquery in the FROM clause, MySQL will return the output from a subquery is used as a temporary table. We called this table as a derived table, inline views, or materialized subquery. The following subquery returns the maximum, minimum, and average number of items in the order table: SELECT Max(items), MIN(items), FLOOR (AVG(items))
All subquery forms and operations supported by the SQL standard will be supported in MySQL also. Subqueries should always use in parentheses. If the main query does not have multiple columns for subquery, then a subquery can have only one column in the SELECT command.
Why don't you do this:
SELECT m.col1, (SELECT SUM(col5) FROM table WHERE col2 = m.col1) FROM table AS m If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
