CODEWITHSUNDEEP
sql-serversql-server-2008
Elliot Worth
Elliot Worth

Reputation: 57

Adding results from two queries

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

Upvotes: 0

Views: 152

Answers (3)

Devart
Devart

Reputation: 121952

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

Upvotes: 0

backtrack
backtrack

Reputation: 8144

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

Upvotes: 0

mohan111
mohan111

Reputation: 8865

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

Upvotes: 2

Related Questions