union query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

union query

Author

Topic

jlu
Starting Member

2 Posts

Posted - 2007-03-01 : 04:00:56

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
coc_ctrc coc_pbp
CAR000001 750.00
CEN000001 750.00
R01000001 900.00
CAR000002 400.00
CEN000002 750.00

tbl_pay
coc_ctrc pay_Amt
CAR000001 1500.00
CEN000001 1000.00
CEN000001 900.00

i need to sum the premium from tbl_pbp table and also sum the claim amt from tbl_pay table to produce a report with the following result :

coc_pbp pay_amt
CAR 1150.00 1500.00
CEN 1500.00 1900.00
R01 900.00 0.00

note : the region represent the left(coc_ctrc,3) of policy no. from both table.

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

and got the following result :

CAR 1150.0000 3000.0000
CEN 3000.0000 3800.0000
R01 900.0000 NULL

also tried using UNION :
SELECT LEFT(COC_CTRC,3) AS REG, SUM(COC_PBP) AS PBP FROM TBL_PBP GROUP BY LEFT(COC_CTRC,3) UNION SELECT LEFT(COC_cTRC,3) AS REG, SUM(PAY_AMT) AS PAY FROM TBL_PAY GROUP BY LEFT(COC_cTRC,3)

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

any help will be greatly appreciated, thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 04:31:52

[code]
select a.policy_no, a.coc_pbp, b.pay_Amt
from
(
select policy_no = left(coc_ctrc, 3),
coc_pbp = sum(coc_pbp)
from tbl_pbp
group by left(coc_ctrc, 3)
) a
inner join
(
select policy_no = left(coc_ctrc, 3),
pay_Amt = sum(pay_Amt)
from tbl_pay
group by left(coc_ctrc, 3)
) b
on a.policy_no = b.policy_no
[/code]

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-03-01 : 05:23:39

You could also do this with Common Table Expressions:


WITH    Agg_pbp (coc_ctrc, Agg_pbp)
        AS
        (
            SELECT   LEFT(p.coc_ctrc, 3)  AS coc_ctrc, 
                     SUM(p.coc_pbp)       AS Agg_pbp
            FROM     tbl_pbp AS p
            GROUP BY LEFT(p.coc_ctrc, 3)
        ), 
        Agg_pay (coc_ctrc, Agg_pay)
        AS
        (
            SELECT   LEFT(p.coc_ctrc, 3)  AS coc_ctrc, 
                     SUM(p.pay_Amt)       AS Agg_pay
            FROM     tbl_pay AS p
            GROUP BY LEFT(p.coc_ctrc, 3)        
        )
SELECT  pbp.coc_ctrc, 
        pbp.Agg_pbp, 
        pay.Agg_pay
FROM    Agg_pbp AS pbp
JOIN    Agg_pay AS pay
ON      pbp.coc_ctrc = pay.coc_ctrc

Mark

jlu
Starting Member

2 Posts

Posted - 2007-03-02 : 02:14:07

khtan, sir, thanks, it works ! finally ! appreciate very much,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 03:22:03

Mark, a completely different question.
How are CTE's execution/query plans stored/cached compared to not using CTE's?
Are they treated exactly as any other query in the stored procedure?

Peter Larsson
Helsingborg, Sweden

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-02 : 13:13:43

Peso,

I realize you are asking about caching and such, but I did find this article that I thought was kind of intersting pertaining to Table Variables and CTEs:

[url]http://jdixon.dotnetdevelopersjournal.com/cte_performance.htm[/url]

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 14:11:39

Thank you!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources