# Sum a one to many based on multiple criteria and JOINS

I'm not sure how to word my question. But I have 2 different tables that need to join together to give results.

``````Table A
--------------------------
Id  |  Field_Id |  Value
--------------------------
1234|     16    |   PO456
1234|      7    |   40
1334|     16    |   PO456
1334|      7    |   20
1246|     16    |   PO679
1246|      7    |   100
1247|     16    |   PO679
1247|      7    |   20
1247|     16    |   PO679
1247|      7    |   40

Table B
--------------
Id  |  Hours |
--------------
1234|   2
1234|   3
1234|   4
1234|   5
1334|   8
1334|   8
1334|   8
1246|   3
1246|   3
1247|   8
1247|   8
``````

Now the Result I want should look like this.

``````Result
-----------------------
Value  |  Est |  Hours
-----------------------
PO456  |   60 |   38
PO679  |   160|   22
``````

Here is my sql:

``````select a.value as "Id", CAST(sum(d.value) AS Decimal(10,2)) as 'Est'
, COALESCE(sum(b.Hours),0) as "Hours"

from table_a a
left Join table_b b ON a.Id = b.Id
inner join table_a d ON d.Id = a.Id AND d.Field_Id = '7'
where a.Field_Id =  '16' and a.value <> ' '
Group By a.value
ORDER BY a.value  ASC
``````

Now my hours column comes in correctly, however it's the Est column that comes back with strange data, like the numbers are thousands off. Is it not being tied back to the first column somehow? I basically want to get a sum of all Id's with that same a.value on one line. My work around right now is it displays all Id's and there are simply multiple Values and it's broken down individually. However, I want it condensed. Any assistance would be appreciated. If there are minor spelling errors in my code (this isn't the actual code, the code is simply renamed privacy reasons, I need help on the logic). Please and thank you.

• The column Value can't be sum()-ed, since it's not a numeric column. The query is invalid and should raise an error. – jarlh Nov 8 at 15:21
• Which dbms are you using? – jarlh Nov 8 at 15:23
• @jarlh That's why I'm casting it as a decimal. The query works, just the value return doesn't. – dcary Nov 8 at 15:31
• You cast the sum, not the values. Do sum(cast(d.value as Decimal(10,2)) instead. – jarlh Nov 8 at 15:33
• @jarlh this didn't give a different result. The numbers are still inaccurate. – dcary Nov 8 at 15:53

What a confusing data model! You can do this with a subquery to get the `est` value:

``````select a16.value, a7.value,
sum(b.hours)
from table_a a16 left join
(select a16.value,
sum(a7.value) as est    -- should convert to a number for sum()
from table_a a16 join
table_a a7
on a16.id = a7.id and a7.field_id = 7 and
a16.field_id = 16
group by 16.value
) a7
on a16.value = a7.value
table_b b
on a16.Id = b.Id
where a16.field_id = 16
group by a16.value, a7.est;
``````
• I put this in, but now all values are Null for the Est column – dcary Nov 8 at 15:47
• sorry I took the group by out and it only returned 1 single record, which is also what I don't want. I want all the different ones. But I didn't receive a syntax error. Just a column that says NULL in it. – dcary Nov 8 at 16:31
• @dcary . . . It should WORK with the `group by`. – Gordon Linoff Nov 8 at 17:51