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

​x
 
SELECT     sum(a.length),    (        SELECT            a.id, b.names, a.length        FROM a        INNER JOIN b ON (a.id= b.id)    )FROM aGROUP BY a.idORDER 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]

 
SELECT b.[name],    SUM(a.[length]) [length]FROM aINNER JOIN b ON a.ID = b.IDGROUP BY b.[name]ORDER BY b.[name]​

Advertisement

Answer