Substrating the columns from two queries

Question

Post reply

Substrating the columns from two queries

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 23, 2012 at 8:57 pm

#260540

Hi,
I have one query as SELECT A FROM TEMP1.
And the other query as SELECT B FROM TEMP2.
Now i want the answer to be shown as A-B.
I don't know how to subtract this 2 queries.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

MSzI SSCommitted Points: 1529 More actions · Answer 1

Try this:

SELECT DIFF = (SELECT A FROM TEMP1) - (SELECT B FROM TEMP2)

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 2

When i tried the above query i was getting the below error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

vinu512 SSCoach Points: 15729 More actions · Answer 3

You can do it as follows:

--Creating Tables

Create table Ex

(Value int )

Create Table Ex1

(Value int )

--Inserting Sample Data

Insert Into Ex

Select 1

Union ALL

Select 2

Union ALL

Select 3

Union ALL

Select 4

Union ALL

Select 5

Insert Into Ex1

Select 6

Union ALL

Select 7

Union ALL

Select 8

Union ALL

Select 9

Union ALL

Select 10

--Query For Your Requirement

Select (b.Value - a.Value) As Diff From

(

Select Value, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From Ex

) As a

JOIN

(

Select Value, ROW_NUMBER() Over (Order By (Select NULL) ) As rn From Ex1

) As b On a.rn = b.rn

You just need to add row number to your query to provide a column to JOIN on and then JOIN the two result sets as derived tables and select the difference of the required fields.

Hope this helps.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Dwain Camps SSC Guru Points: 86948 More actions · Answer 4

Since row ordering is not guaranteed by SQL, how do you know you're subtracting the right numbers at each row?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

vinu512 SSCoach Points: 15729 More actions · Answer 5

dwain.c (8/24/2012)
Since row ordering is not guaranteed by SQL, how do you know you're subtracting the right numbers at each row?

Yes it should be ordered according to the OP's requirement. I used Select NULL just to show an example. My bad, should have mentioned it in my reply.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Shadab Shah (8/23/2012)
Hi,
I have one query as SELECT A FROM TEMP1.
And the other query as SELECT B FROM TEMP2.
Now i want the answer to be shown as A-B.
I don't know how to subtract this 2 queries.

SELECT TOP 5 [A] = ROW_NUMBER() OVER(ORDER BY [name]) INTO #TEMP1 FROM sys.columns ORDER BY ([name])

SELECT TOP 4 = ROW_NUMBER() OVER(ORDER BY [name]) INTO #TEMP2 FROM sys.columns ORDER BY ([name])

SELECT DIFF = (SELECT SUM(A) FROM #TEMP1) - (SELECT SUM(B) FROM #TEMP2)

This probably isn't what you are expecting to see - can you provide more detail?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

ChrisM@Work (8/24/2012)
Shadab Shah (8/23/2012)
Hi,
I have one query as SELECT A FROM TEMP1.
And the other query as SELECT B FROM TEMP2.
Now i want the answer to be shown as A-B.
I don't know how to subtract this 2 queries.
SELECT TOP 5 [A] = ROW_NUMBER() OVER(ORDER BY [name]) INTO #TEMP1 FROM sys.columns ORDER BY ([name])
SELECT TOP 4 = ROW_NUMBER() OVER(ORDER BY [name]) INTO #TEMP2 FROM sys.columns ORDER BY ([name])
SELECT DIFF = (SELECT SUM(A) FROM #TEMP1) - (SELECT SUM(B) FROM #TEMP2)
This probably isn't what you are expecting to see - can you provide more detail?

I certainly wasn't what I was expecting to see. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

dwain.c (8/24/2012)
Grant Fritchey (8/24/2012)
Jeff Moden (8/23/2012)
ChrisM@Work (8/23/2012)
Brandie Tarvin (8/23/2012)
... Tootsie Rolls...
Are we back on Clinton??
It Depends... are you wearing a blue dress? :-):-D:-P:hehe:
I thought we had an agreement. You wear the dress. I just wear the skirt.
It certainly wasn't what I was expecting to see. 😛

Nah mate me neither. Takes all kinds.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden