Get sum without group by but with subquery

group-by, mysql, subquery, sum

Solution

To get your current SQL to work using GROUP BY you would need the following (ie the SUM aggregate function):-

select     NAME, SUM(BAL)
from VS, BALANCE 
WHERE VS.V_ID = BALANCE.V_ID
group by NAME

Note that this is just your original SQL minimally modified to work and it still uses the implicit join you coded. Implicit joins should generally be avoided and better to use explicit INNER JOIN....ON syntax:-

select NAME, SUM(BAL)
from VS
INNER JOIN BALANCE 
ON VS.V_ID = BALANCE.V_ID
group by NAME

If you really want to avoid the GROUP BY then it is possible using a sub query, but is likely to be slower (as it effectively has to perform an extra query for every row tha main query returns):-

select NAME, (SELECT SUM(BAL) FROM BALANCE WHERE BALANCE.V_ID = VS.V_ID)
from VS

EDIT, in response to your comment on sub queries.

Correlated sub queries effectively force MySQL to get a result set, and then for each row on the result set to perform another query. Most of the time they are used to get an aggregate value (such as the max value of a field related to a row on the returned row, but where GROUP BY on the main query would not be viable).

For example if you had a list of comments for a user, but on each row you wanted to know the date of the latest comment from that user you might do the following:-

SELECT users.user_name, comments.comment_date, (SELECT MAX(comment_date) FROM comments WHERE comments.user_id = users.id) AS latest_comment_date
FROM users
INNER JOIN comments
ON users.id = comments.user_id

This could be written to do a non correlated sub query using GROUP BY which is then joined:-

SELECT users.user_name, comments.comment_date, latest_comment_date
FROM users
INNER JOIN comments
ON users.id = comments.user_id
INNER JOIN
(
    SELECT user_id, MAX(comment_date) AS latest_comment_date
    FROM comments
    GROUP BY user_id
) sub1
ON users.id = sub1.user_id

If you are dealing with a large number of records on users this would likely be faster.

However if you were only dealing with a tiny number of records on users (and determining that number was quite complex), getting ALL the max comment dates would be an unnecessary overhead, and it forces a join against a sub query which isn't likely to use indexes.

Problem

i have two tables for example: ``` BALANCE VS ╔══════╦══════╗ ╔══════╦═══════╗ ║ V_ID ║ BAL ║ ║ V_ID ║ NAME ║ ╠══════╬══════╣ ╠══════╬═══════╣ ║ 1 ║ 1000 ║ ║ 1 ║ Carl ║ ║ 1 ║ 500 ║ ║ 2 ║ Peter ║ ║ 1 ║ -200 ║ ╚══════╩═══════╝ ║ 2 ║ 350 ║ ║ 2 ║ 1000 ║ ╚══════╩══════╝ ``` Now i've done a query like: ``` select NAME,sum(BAL) from VS,BALANCE where VS.V_ID = BALANCE.V_ID group by NAME ``` And the result is: ``` ╔═══════╦══════╗ ║ NAME ║ BAL ║ ╠═══════╬══════╣ ║ Carl ║ 1300 ║ ║ Peter ║ 1350 ║ ╚═══════╩══════╝ ``` But i'd like to know how its done without the ´group by´ clause. Something like a subquery. I tried different things but i cant figure out how it would work. I always end with results like all the values summed up into one row or results like the above one but without grouping. may be anyone could help me greetings edit:forgot sum()

Original source