Combining the results of two SQL queries as separate columns

Question

I have two queries which return separate result sets, and the queries are returning the correct output.

How can I combine these two queries into one so that I can get one single result set with each result in a separate column?

Query 1:

SELECT SUM(Fdays) AS fDaysSum From tblFieldDays WHERE tblFieldDays.NameCode=35 AND tblFieldDays.WeekEnding=?

Query 2:

SELECT SUM(CHdays) AS hrsSum From tblChargeHours WHERE tblChargeHours.NameCode=35 AND tblChargeHours.WeekEnding=?

Thanks.

SELECT SUM("+Fdays+")AS fDaysSum, SUM("+CHdays+") AS hrsSum? — David Starkey
– David Starkey, Commented May 3, 2013 at 17:06
You want a join or a union? Perhaps you can show some example data and desired output. — Tim Lehner
– Tim Lehner, Commented May 3, 2013 at 17:12
@DavidStarkey I tried it in the same way but i dont know how to give different where conditions for different DB — W A K A L E Y
– W A K A L E Y, Commented May 3, 2013 at 17:17
@TimLehner I need union. I tried Query_1 UNION ALL Query_2 it works but the Result set give 2 rows with 1 column instead of which I want 2 columns and 1 rows i.e. both values in sible rs. next() call. — W A K A L E Y
– W A K A L E Y, Commented May 3, 2013 at 17:20
@user1614217 Then you don't need a UNION, you need a CROSS JOIN. — Lamak
– Lamak, Commented May 3, 2013 at 17:23

Willy Pt · Accepted Answer · 2013-05-03 17:26:04Z

97

You can aliasing both query and Selecting them in the select query
http://sqlfiddle.com/#!2/ca27b/1

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

answered May 3, 2013 at 17:26

Willy Pt

1,85513 silver badges20 bronze badges

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

10 Comments

W A K A L E Y Over a year ago

I have one more issue. consider that i am getting values from first query as X and i need to use one of that value in Query 2 how it can be done?

Willy Pt Over a year ago

Is the query2 on a different line of php? you can put the (SELECT...) as x to your new query and use it as parameter from the alias (x.a).. Or if it's on different line on php, consider storing the result of your needed value and use it as a parameter when exexuting your sql query on PHP

W A K A L E Y Over a year ago

I thought the same and tried it gives me Error: Too Few Parameter

Willy Pt Over a year ago

can you update your question with your current query? and the error?

W A K A L E Y Over a year ago

Its on the same line. I tried to use it as alias it gives me Error: Too Few Parameter

|

Lamak · Accepted Answer · 2013-05-03 17:34:37Z

17

You can use a CROSS JOIN:

SELECT *
FROM (  SELECT SUM(Fdays) AS fDaysSum 
        FROM tblFieldDays 
        WHERE tblFieldDays.NameCode=35 
        AND tblFieldDays.WeekEnding=1) A -- use you real query here
CROSS JOIN (SELECT SUM(CHdays) AS hrsSum 
            FROM tblChargeHours 
            WHERE tblChargeHours.NameCode=35 
            AND tblChargeHours.WeekEnding=1) B -- use you real query here

edited May 3, 2013 at 17:34

answered May 3, 2013 at 17:18

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

5 Comments

Tim Lehner Over a year ago

At first I was wondering if this was a joke, but it is a solution if the queries will always only return one row (obviously will with only aggregates) and the OP doesn't mind putting those params in twice.

Lamak Over a year ago

@TimLehner No joke here. And even if I saw that the derived tables return one row, I should've clarified that this works fine because of it

W A K A L E Y Over a year ago

@TimLehner It give me Error which says Synatx Error in FROM clause Here is my code:

String t1 = "SELECT * "+ "FROM (  SELECT SUM("+Fdays+")AS fDaysSum "+          "FROM tblFieldDays "+         "WHERE tblFieldDays.NameCode=35 AND tblFieldDays.WeekEnding=?) "+ "CROSS JOIN (SELECT SUM("+CHdays+") AS hrsSum "+              " FROM tblChargeHours WHERE tblChargeHours.NameCode=35 AND tblChargeHours.WeekEnding=?)";

Lamak Over a year ago

@user1614217 Maybe it was the alias for the first query, I didn't have an space there. Try now my updated answer

W A K A L E Y Over a year ago

@Lamak Same issue, Infact i did the spacing thing earlier also

AS4noob · Accepted Answer · 2017-11-20 17:43:34Z

You could also use a CTE to grab groups of information you want and join them together, if you wanted them in the same row. Example, depending on which SQL syntax you use, here:

WITH group1 AS (
  SELECT testA
    FROM tableA
),
group2 AS (
  SELECT testB
    FROM tableB 
)
SELECT *
  FROM group1
  JOIN group2 ON group1.testA = group2.testB --your choice of join
;

You decide what kind of JOIN you want based on the data you are pulling, and make sure to have the same fields in the groups you are getting information from in order to put it all into a single row. If you have multiple columns, make sure to name them all properly so you know which is which. Also, for performance sake, CTE's are the way to go, instead of inline SELECT's and such. Hope this helps.

ramreddy aredla · Accepted Answer · 2017-01-28 09:24:52Z

-2

how to club the 4 query's as a single query

show below query

total number of cases pending + 2.cases filed during this month ( base on sysdate) + total number of cases (1+2) + no. cases disposed where nse= disposed + no. of cases pending (other than nse <> disposed)

nsc = nature of case

report is taken on 06th of every month

( monthly report will be counted from 05th previous month to 05th present of present month)

answered Jan 28, 2017 at 9:24

ramreddy aredla

1

Collectives™ on Stack Overflow

Combining the results of two SQL queries as separate columns

4 Answers 4

10 Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related