SQL result from table a but name from table b

My request is quiet simple however I can’t find it (maybe I can’t goolge correctly). I have Table A and Table B

Table A:

ID	length
1	50
2	45
2	20

and Table B

ID	name
1	apple
2	orange

I want to get the sum of all lengths of Table A with unique ID but with Table B’s names

Name	length
apple	50
orange	65

I tried the following:

SELECT 
    sum(a.length),
    (
        SELECT
            a.id, b.names, a.length
        FROM a
        INNER JOIN b ON (a.id= b.id)
    )
FROM a
GROUP BY a.id
ORDER BY a.id

I have a feeling i made it more complicated than it has to be.

Answer

You don’t need a subquery:

SELECT b.[name],
    SUM(a.[length]) [length]
FROM a
INNER JOIN b ON a.ID = b.ID
GROUP BY b.[name]
ORDER BY b.[name]

Advertisement

Answer