Adding results from two queries

Question

I'm using MS-SQL 2008 R2.

I have 2 Queries which are returning the required results. But I need to add the two results from each queries to provide a final value [Enterprise Value]. I'm sure this is very straight forward but I'm going round in circles on this, have tried incorporating SUM which I think is the right approach?

Here is the full query as it currently stands:

declare @d1 datetime='2015-12-22'
(select 
c.fs_perm_sec_id,
((c.p_price * s.p_com_shs_out)/1000) as [Enterprise Value]
from fp_v1.fp_basic_bd c
left join edm_v1.edm_security_entity_map e 
on e.fs_perm_sec_id= c.fs_perm_sec_id
left join fp_v1.fp_basic_sho s 
on s.fs_perm_sec_id = c.fs_perm_sec_id
and c.date=@d1
where s."date" = 
    (
    select MAX(s2."date")
    from fp_v1.fp_basic_sho s2
    where s2.fs_perm_sec_id=c.fs_perm_sec_id
    and s2."date" <= c."date"
    )
and c."date"=@d1
and e.termination_date is null
and c.fs_perm_sec_id = 'GPHC8W-S-GB') 

UNION ALL

select 
ff.fs_perm_sec_id,
((FF_debt + ff_pfd_stk + ff_min_int_accum) - FF.ff_cash_st) as [Enterprise Value]
from ff_v2.ff_basic_af_v2 FF
where FF."date" = 
(   select MAX(FF2."date")
    from ff_v2.ff_basic_af_v2 FF2
    where FF2.fs_perm_sec_id=FF.fs_perm_sec_id
    and FF.date <= FF2.date
    ) 
and FF.fs_perm_sec_id =('GPHC8W-S-GB')

When inserting a "UNION ALL" between the two queries I get the following results:

fs_perm_sec_id  Enterprise Value
GPHC8W-S-GB     9270.5204655
GPHC8W-S-GB     835

What I would like to achieve is a sum of the two values brought onto one row, i.e.:

fs_perm_sec_id  Enterprise Value
GPHC8W-S-GB     10105.52

Thanks for your help.

Final SQL:

declare @d1 datetime='2015-12-23'
Select  fs_perm_sec_id, SUM([Enterprise Value]) AS 'Enterprise Value'

from 
(
(select 
c.fs_perm_sec_id,
((c.p_price * s.p_com_shs_out)/1000) as [Enterprise Value]
from fp_v1.fp_basic_bd c
left join edm_v1.edm_security_entity_map e 
on e.fs_perm_sec_id= c.fs_perm_sec_id
left join fp_v1.fp_basic_sho s 
on s.fs_perm_sec_id = c.fs_perm_sec_id
and c.date=@d1
where s."date" = 
    (
    select MAX(s2."date")
    from fp_v1.fp_basic_sho s2
    where s2.fs_perm_sec_id=c.fs_perm_sec_id
    and s2."date" <= c."date"
    )
and c."date"=@d1
and e.termination_date is null
and c.fs_perm_sec_id in ('FT9TC5-S-GB','GPHC8W-S-GB','R85KLC-S-US'))

UNION ALL

select 
ff.fs_perm_sec_id,
((FF_debt + ff_pfd_stk + ff_min_int_accum) - FF.ff_cash_st) as [Enterprise Value]
from ff_v2.ff_basic_af_v2 FF
where FF."date" = 
(   select MAX(FF2."date")
    from ff_v2.ff_basic_af_v2 FF2
    where FF2.fs_perm_sec_id=FF.fs_perm_sec_id
    and FF.date <= FF2.date
    ) 
and FF.fs_perm_sec_id in ('FT9TC5-S-GB','GPHC8W-S-GB','R85KLC-S-US')) t
group by t.fs_perm_sec_id

just use this derived table Select fs_perm_sec_id,SUM(Enterprise Value) from (your whole code) GROUP BY fs_perm_sec_id — mohan111
– mohan111, Commented Dec 23, 2015 at 11:17

mohan111 · Accepted Answer · 2015-12-23 11:18:48Z

2

just use the Derived Table and Group by

 Select  fs_perm_sec_id,
        SUM(Enterprise Value) EnterpriseValue 

    from (**your whole code**) 
GROUP BY  fs_perm_sec_id

answered Dec 23, 2015 at 11:18

mohan111

8,9054 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Elliot Worth Over a year ago

Thanks, still having problems. The Enterprise Value, shouldn't it be like this: SUM([Enterprise Value]) EnterpriseValue ? I then get incorrect syntax near the keyword 'group'

mohan111 Over a year ago

keep it as SUM([Enterprise Value]) AS 'Enterprise Value'

Community · Accepted Answer · 2017-05-23 11:45:52Z

0

use group by

How to use group by with union in t-sql

SELECT  id,sum(*)
FROM    ( SELECT    id,
                    time
          FROM      dbo.a
          UNION
          SELECT    id,
                    time
          FROM      dbo.b
        )
GROUP BY id

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Dec 23, 2015 at 11:21

backtrack

8,1545 gold badges56 silver badges103 bronze badges

Comments

Devart · Accepted Answer · 2015-12-23 11:53:22Z

DECLARE 
      @d1 DATE = '20151222'
    , @fs_perm_sec_id VARCHAR(100) = 'GPHC8W-S-GB'

SELECT @fs_perm_sec_id, SUM([Enterprise Value])
FROM (
    SELECT [Enterprise Value]
    FROM (
        SELECT
              c.fs_perm_sec_id
            , (c.p_price * s.p_com_shs_out) / 1000 AS [Enterprise Value]
            , RowNum = ROW_NUMBER() OVER (ORDER BY s.[date] DESC)
        from fp_v1.fp_basic_bd c
        join fp_v1.fp_basic_sho s on s.fs_perm_sec_id = c.fs_perm_sec_id
        left join edm_v1.edm_security_entity_map e on e.fs_perm_sec_id= c.fs_perm_sec_id
        where c.[date] = @d1
            and e.termination_date is null
            and c.fs_perm_sec_id = @fs_perm_sec_id
    ) t
    WHERE t.RowNum = 1

    UNION ALL

    SELECT FF_debt + ff_pfd_stk + ff_min_int_accum - ff_cash_st
    FROM (
        SELECT
              ff.fs_perm_sec_id
            , FF_debt
            , ff_pfd_stk
            , ff_min_int_accum
            , FF.ff_cash_st
            , RowNum = ROW_NUMBER() OVER (ORDER BY FF.[date] DESC)
        FROM ff_v2.ff_basic_af_v2 FF
        WHERE FF.[date] = 
            AND FF.fs_perm_sec_id = @fs_perm_sec_id
    ) t
    WHERE t.RowNum = 2
) t

Collectives™ on Stack Overflow

Adding results from two queries

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related