how to join with group by statement

Question

Im having 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   200
    30   300

and i want sum of all amount group by prodid. can any one give me both ansi and Tsql quries.

Answer 1

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

Answer 2

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;

Answer 3

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;

Answer 4

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