sum from two tables with two columns

[jojopangit]

hi, im jude, im new in sql and still learning, please help me with my problem
i have two tables, tbl_pbp and tbl_pay with the following data :

tbl_pbp
policy no. premium
CAR000001 750.00
CEN000001 750.00
R01000001 900.00
CAR000002 400.00
CEN000002 750.00

tbl_pay
policy no. claim amt
CAR000001 1500.00
CEN000001 1000.00

i need to produce a report with the following result :
region premium claim amt
CAR 1150.00 1500.00
CEN 1500.00 1000.00
R01 900.00 0.00

i tried the following code using JOIN :
SELECT LEFT(TBL_PBP.CO C_CTRC,3),SUM(C OC_PBP) AS PBP, SUM(PAY_AMT) AS PAY FROM TBL_PBP left OUTER JOIN TBL_PAY ON LEFT(TBL_PBP.CO C_CTRC,3) = LEFT(TBL_PAY.CO C_CTRC,3) GROUP BY LEFT(TBL_PBP.CO C_CTRC,3)

and got the following result :
CAR 1150.0000 3000.0000
CEN 1500.0000 2000.0000
R01 900.0000 NULL

also tried using UNION :
select left(coc_Ctrc,3 ),sum(coc_pbp) from tbl_PBP group by left(coc_ctrc,3 ) UNION SELECT LEFT(COC_cTRC,3 ), SUM(PAY_aMT) FROM TBL_PAY group by left(coc_ctrc,3 )

and got the following result :
CAR 1150.0000
CAR 1500.0000
CEN 1000.0000
CEN 1500.0000
R01 900.0000

any help will be greatly appreciated, thanks in advance.

[mabubakarpk]

Your query needs little bit change
as following

SELECT LEFT(TBL_PBP.CO C_CTRC,3),SUM(C OC_PBP) AS PBP, Max(PAY_AMT) AS PAY FROM TBL_PBP left OUTER JOIN TBL_PAY ON LEFT(TBL_PBP.CO C_CTRC,3) = LEFT(TBL_PAY.CO C_CTRC,3)
GROUP BY LEFT(TBL_PBP.CO C_CTRC,3)

[jojopangit]

Your query needs little bit change
as following

SELECT LEFT(TBL_PBP.CO C_CTRC,3),SUM(C OC_PBP) AS PBP, Max(PAY_AMT) AS PAY FROM TBL_PBP left OUTER JOIN TBL_PAY ON LEFT(TBL_PBP.CO C_CTRC,3) = LEFT(TBL_PAY.CO C_CTRC,3)
GROUP BY LEFT(TBL_PBP.CO C_CTRC,3)

thanks for your reply, it works well, but when i add another *row to my tbl_pay table,

CAR000001 1500
CEN000001 1000
* CEN000001 900

i got the result :
CAR 1150.0000 1500.0000
CEN 3000.0000 1000.0000
R01 900.0000 NULL

instead of :
CAR 1150 1500
CEN 1500 1900
R01 900 NULL

thanks again.

[mabubakarpk]

As I understand your problem tbl_Pay should be unique policy# and its amount but you are adding another record which is already exist in your table. You should be unique record in tbl_Pay or you have to add another column like date to make it unique.But now your query will be changed accordingly.

Regards,