Combine results from 2 SQL Server queries into 2 columns

Question

I currently have 2 SQL queries:

select 
    SUM(CASE T1.DOCTYPE 
          WHEN '1' THEN T1.CURTRXAM *1
          WHEN '4' THEN T1.CURTRXAM *-1
          WHEN '5' THEN T1.CURTRXAM *-1
          WHEN '6' THEN T1.CURTRXAM *-1
       END) as [Payables TB]
from PM20000 T1

select
    sum(PERDBLNC) as [GL Balance]
from GL10110
where ACTINDX = '130'

which return 2 results like this:

Payables TB
1520512.30

GL Balance
-1520512.30

I would like to combine the results into 2 columns and have a variance column like below -

Payables TB      GL Balance       Variance
1520512.30       -1520512.30      0.00

Thank you

Do you have some code that illustrates whether or not you have tried something? — rayryeng
– rayryeng, Commented May 28, 2014 at 4:38
perhaps don't assume that a forum post is my first point of call instead of my last resort? I am very familiar with joins but the tables are not related and I have never come across this situation before where my query can't be created with a join. I spent a long time searching forums but as being new to programming and completely self taught I didn't even know the correct terminology for what I was trying to achieve. — Tania Mofflin
– Tania Mofflin, Commented May 30, 2014 at 4:05

Hieu Vo · Accepted Answer · 2014-05-28 04:08:03Z

1

simply

select
    (select 
        SUM(CASE T1.DOCTYPE 
        WHEN '1' THEN T1.CURTRXAM *1
        WHEN '4' THEN T1.CURTRXAM *-1
        WHEN '5' THEN T1.CURTRXAM *-1
        WHEN '6' THEN T1.CURTRXAM *-1
        END) as [Payables TB]
        from PM20000 T1) as Payables TB,

    (select
        sum(PERDBLNC) as [GL Balance]
        from GL10110
        where ACTINDX = '130') as GL Balance,

    0.00 as Variance

answered May 28, 2014 at 4:08

Hieu Vo

3,3441 gold badge33 silver badges32 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:32:24Z

0

You can wrap these into CTE's to reuse the values to compute the difference. With no join condition you will just need to CROSS JOIN, as long as these return just one row each :

WITH Payables AS
(
  SELECT 
    SUM(
      CASE  
        WHEN T1.DOCTYPE IN ('1') THEN T1.CURTRXAM *1
        WHEN T1.DOCTYPE IN ('4','5','6') THEN T1.CURTRXAM *-1
        -- ? ELSE
      END) as [Payables TB]
  FR PM20000 T1
),
Balance AS
(
  SELECT
  SUM(PERDBLNC) as [GL Balance]
  FROM GL10110
  WHERE ACTINDX = '130'
)
SELECT 
  Payables.[Payables TB], 
  Balance.[GL Balance],  
  Payables.[Payables TB] + Balance.[GL Balance] AS Variance
  FROM 
    Payables, Balance; -- OR Payables CROSS JOIN Balance

Since you seem to be doing the same projection for T1.DOCTYPE 4, 5 and 6 in the first query, you can replace it with a CASE WHEN x IN (...)

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered May 28, 2014 at 4:07

StuartLC

108k18 gold badges225 silver badges295 bronze badges

3 Comments

Tania Mofflin Over a year ago

Great thankyou StuartLC I am very familiary with joins but wasn't aware of how to use cross joins when the tables are not related, I will read up on these. Thanks for pointing out the duplication in my CASE I will simplify it using IN.

StuartLC Over a year ago

Out of interest, you surely don't want to hard code the variance like in the accepted answer? The reason I used CTEs (or you could use derived tables) is so that you can re-use the aggregate SUM columns without repeating the query from scratch?

Tania Mofflin Over a year ago

No I definitely don't want the variance hard coded. I am unable to up vote but it says I accepted your answer?

Collectives™ on Stack Overflow

Combine results from 2 SQL Server queries into 2 columns

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related