Yes,

>Try

SELECT sum(m.rgcur),
>   (SELECT sum(t.tax_cur) FROM py_ehtax t
>        WHERE t.ehmast_id = m.ehmast_id ),
>   (SELECT sum(h.hours) FROM py_ehhour h
>        WHERE h.ehmast_id = m.ehmast_id )
>FROM py_ehmast m
>GROUP BY ee_id
>

That gives me the error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'm.ehmast_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I believe this is because the query is group by the ee_id field, this means that the parser doesn't know which ehmast_id to use for the subquerys.

Also, we had it similar to above using a select to get the value of each field we needed but after 17 hours of processing it was too slow.

What I have done, and it seems to work is create two derived tables and join them to the tax table. The derived tables have the summed data for each ee_id and I just use now the min() of the field from the derived tables so it won't sum the sum, since there are multiple tax records for each ee_id.

Thanks for the help, would still like to hear other ideas.

BOb


>>Hi All,
>>
>>I have a query where I want to sum() data from three tables. Heres the problem though. I have a 'parent' record which has some values in it, then two child tables...
>>
>>So, if I do my first query:
>>
>>SELECT sum(rgcur) from py_ehmast
>>GROUP BY ee_id
>>
>>I get the correct value. Then I add the child table..
>>
>>SELECT sum(m.rgcur), sum(t.tax_cur)
>>FROM py_ehmast m
>>JOIN py_ehtax t ON m.ehmast_id = t.ehmast_id
>>GROUP BY ee_id
>>
>>Now, I get the correct tax_cur sum, but the m.rgcur sum is overstated.
>>
>>Then when I add the third table...
>>
>>SELECT sum(m.rgcur), sum(t.tax_cur), sum(h.hours)
>>FROM py_ehmast m
>>JOIN py_ehtax t ON m.ehmast_id = t.ehmast_id
>>JOIN py_ehhour h on m.ehmast_id = h.ehmast_id
>>GROUP BY ee_id
>>
>>Of course, I get even different numbers... I know WHY this is happening, I am wondering what the easiest way to solve this is, without doing three selects?
>>
>>Thanks,
>>BOb