Multiple table joins with aggregate (mssql / sql server)

Question

Very simple question. What I want to do is select all columns from one table and sum of one column (which could have multiple matching rows) from another table.

Example:

table ta (eid, uid, name, year, etc, etc, etc)
table tb (eid, uid, name, year, amount, etc, etc)

eid - will not match between both table uid, name, year - will match across both tables

So I want to pull out all columns from table ta, simple:

select * from ta where eid='value';

I want to join amount column from table tb to my resultset, simple:

select a.*, b.amount
from ta a
inner join tb b on a.year=b.year
where a.eid='value';

Great, this works fine. But what if I have multiple rows in table tb?

Executing:

select a.*, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value';

gives me the following error:

Column 'ta.eid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So I add:

select a.*, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value' group by ta.uid;

And I get the same error!

However, if I change my query to:

select a.uid, a.year, sum(b.amount)
from ta a inner join tb b on a.uid=b.uid
where a.year='value'
group by ta.uid, ta.year;

It works, but now I have three columns instead of all columns that I wanted.

So, at this point my question becomes: Is there a better, cleaner way of structuring this query other than me manually typing out all columns I want to pull from two tables with GROUP BY clause?

Answer 1

You can preaggregate in a subquery:

select a.*, b.sumb
from ta a left join
     (select b.uid, sum(b.amount) as sumb
      from tb b
      group by b.uid
     ) b
     on a.uid=b.uid
where a.year = 'value';