10

I'm having some trouble figuring out any way to combine two SQL queries into a single one that expresses some greater idea.

For example, let's say that I have query A, and query B. Query A returns the total number of hours worked. Query B returns the total number of hours that were available for workers to work. Each one of these queries returns a single column with a single row.

What I really want, though, is essentially query A over query B. I want to know the percentage of capacity that was worked.

I know how to write query A and B independently, but my problem comes when I try to figure out how to use those prewritten queries to come up with a new SQL query that uses them together. I know that, on a higher level, like say in a report, I could just call both queries and then divide them, but I'd rather encompass it all into a single SQL query.

What I'm looking for is a general idea on how to combine these queries using SQL.

Thanks!

Improve this question
1
  • Rather than trying answer this abstractly, post your queries and let's deal with this specific case. Commented Dec 14, 2010 at 16:40

4 Answers 4

Reset to default
11

Unconstrained JOIN, Cartesian Product of 1 row by 1 row

SELECT worked/available AS PercentageCapacity
FROM ( SELECT worked FROM A ), 
     ( SELECT available FROM B )
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I essentially ended up using this. What I found very important though was aliasing the columns as well as the rows. I was confusing myself on that point.
4

You can declare variables to store the results of each query and return the difference:

DECLARE @first INT
DECLARE @second INT

SET @first = SELECT val FROM Table...
SET @second = SELECT val FROM Table...

SELECT @first - @second
Improve this answer

Comments

2

The answer depends on where the data is coming from.

If it's coming from a single table, it could be something as easy as:

select totalHours, availableHours, (totalHours - availableHours) as difference
from hoursTable

But if the data is coming from separate tables, you need to add some identifying column so that the rows can be joined together to provide some useful view of the data.

You may want to post examples of your queries so we know better how to answer your question.

Improve this answer

1 Comment

I redid the question with more details and specifics on what database I'm actually working with. stackoverflow.com/questions/4474364/…
2

You can query the queries:

SELECT
  a.ID
  a.HoursWorked/b.HoursAvailable AS UsedWork
FROM
  ( SELECT ID, HoursWorked FROM Somewhere ) a
INNER JOIN
  ( SELECT ID, HoursAvailable FROM SomewhereElse ) b
ON
  a.ID =  b.ID
Improve this answer

2 Comments

Of course be careful with this is these are integers or you will have bad math.
Depending on your query and table structure, it might be that you shouldn't join on ID. I ended up in my query that had GROUP BY user_id to join on user_id and instead of using INNER JOIN, I ended up using RIGHT JOIN (or LEFT JOIN depending on query order) so that the b-query is not constrained by a-query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.