How to join with group by statement

I have two tables

TableA

Custid prodid Amt1
  1      10   200
  2      20   300
  3      10   400
  4      30   300
  5      20   400
  6      30   200

TableB

prodid  Amt2
    10   100
    20   20开发者_开发问答0
    30   300

and I want to sum of all amount group by prodid. Can anyone give me both ansi and Tsql queries?

SELECT a.prodid,sum(a.Amt1 + b.Amt2) as AmtSum 
     FROM TableA a JOIN TableB b ON (a.prodid = b.prodid) 
     GROUP BY a.prodid;

What about that? :-)

SELECT SUM(a.Amt1),SUM(b.Amt2)
FROM (TableA AS a INNER JOIN TableB AS b ON a.prodid = b.prodid)
GROUP BY a.prodid

I believe this will work anywhere:

SELECT a.Custid,a.prodid,sum(a.Amt1 + b.Amt2) as AmtSum FROM TableA a JOIN TableB b ON (a.prodid = b.prodid) GROUP BY a.Custid,a.prodid;

try this... I think it will also work if some product id not in TABLEB

SELECT a.prodid,SUM(a.Amt1),SUM(b.Amt2)
FROM TableA AS a left outer JOIN TableB AS b ON a.prodid = b.prodid
GROUP BY a.prodid