3

Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables:

SELECT SUM(A) A, SUM(B) B FROM X
SELECT SUM(C) C, SUM(D) D FROM Y

I'd like to combine these queries into a single resultset that contains A, B, C, D.

What's a good way to do this within a single query, as opposed to multi-query solutions like selecting the results into and out of scalar variables?

6

Two subqueries?

SELECT Q1.A, Q1.B, Q2.C, Q2.D
FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B)
CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);
  • 1
    Or CROSS JOIN of course but hardly worth a separate answer. – Martin Smith Jun 29 '15 at 8:02
  • @martin, yes but my fingers are used to typing cross apply, I almost never type cross join. – Mister Magoo Jun 29 '15 at 10:00
  • @MartinSmith - I tried to get the CROSS JOIN syntax to work with MySQL - no joy! Could you show me (us?) how that syntax would work with this problem? – Vérace Jun 29 '15 at 15:03
  • 1
    @Vérace just replace CROSS APPLY with CROSS JOIN. (You will also need to replace Q1(A,B) with Q1 and Q2(C,D) with Q2. This is not needed in SQL-Server either but will give you error in MySQL.) – ypercubeᵀᴹ Jun 29 '15 at 15:11
  • Seems that the "CROSS JOIN" is redundant - it can just be replaced with a comma, as per @Lennart 's answer to my other question. Thanks for your comment on my (now deleted!) answer - brain burp! Which would be more efficient - the CROSS JOIN or the SELECT NULL syntax of Lennart? – Vérace Jun 29 '15 at 15:22
3

Here's one way:

SELECT MAX(A), MAX(B), MAX(C), MAX(D)
FROM (
    SELECT SUM(A) AS A, SUM(B) AS B, NULL AS C, NULL AS D FROM X
    UNION ALL
    SELECT NULL AS A, NULL AS B, SUM(C) AS C, SUM(D) AS D FROM Y
) AS T

You may have to cast NULL to whatever type A, B, C and D have

  • Brilliant - also has the advantage of running in MySQL - racking my brains last night as to how to get this to work. How would I do this using a CROSS JOIN in MySQL? – Vérace Jun 29 '15 at 15:02
  • @Vérace, something like: select A, B, C, D from ( SELECT SUM(A) as A, SUM(B) as B FROM X ) as U, ( SELECT SUM(C) as C, SUM(D) as D FROM Y ) as Vshould work I guess – Lennart Jun 29 '15 at 15:14
  • Works with an explicit CROSS JOIN as well: select A, B, C, D from ( SELECT SUM(A) as A, SUM(B) as B FROM X ) as U CROSS JOIN ( SELECT SUM(C) as C, SUM(D) as D FROM Y ) as V – Lennart Jun 29 '15 at 15:25
  • Indeed - as I found out, replacing CROSS JOIN with a comma works - you've done the inverse!:-) – Vérace Jun 29 '15 at 15:26
  • I noticed the discussion on Mister Magoo's answer after I posted the comment :-) – Lennart Jun 29 '15 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.