April 16, 2008 at 9:46 am
Hello everyone,
I'm using Sql Server Express and have created two tables. I need help on getting the difference(sum +/-) from two rows on different tables. Is this possible?
Example: Table1 has 1,2,3,4,5 and Table2 has 2,3,4,5,6 and I would like to calculate the difference from Table1 and Table2.
Please advice.
April 16, 2008 at 10:37 am
select Col1 from Table1
EXCEPT
select Col1 from Table2
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2008 at 11:02 am
Thank you for your reply. I've tried the EXCEPT and it only returns the value that was on table1. I need it to calculate the difference.
If column1 has a value of 5 from table1 and column1 has a value of 3 from table2, I need the difference which would be "2 or -2".
Any suggestion?
April 16, 2008 at 11:15 am
There are a couple of ways to do this. One is:
select sum(col1), sum(col2),...
from
(select col1, col2, ...
from dbo.table1
union all
select -1 * col1, -1 * col2, ...
from dbo.table2) Sub
That will give you the total of all columns, all rows, with table 1 returning positive values and table 2 negative values.
Another would be:
select t1.col1 - t2.col1, t1.col2 - t2.col2
from dbo.table1 t1
inner join dbo.table2 t2
on t1.id = t2.id
This one assumes there is a relationship between the two tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 11:18 am
Are you talking about this?
select Tot1 - Tot2 as Diff
from
(select sum(field1) Tot1 from Table1) t1
CROSS JOIN
(select sum(field2) Tot2 from Table2) t2
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 11:28 am
gtan018 (4/16/2008)
Thank you for your reply. I've tried the EXCEPT and it only returns the value that was on table1. I need it to calculate the difference.
This IS the difference of Table1 - Table2. Tables are sets and the set-theoretic difference operation is implemented by EXECPT.
If column1 has a value of 5 from table1 and column1 has a value of 3 from table2, I need the difference which would be "2 or -2".
Apparently you want scalar subtraction of the column values from the different tables. Unfortunately, this is not yet logical, because now you need a way to correlate the rows from the two tables.
For instance in your orginal example you had multiple values in both: (1,2,3,4,5) and (2,3,4,5,6), but which numbers in the second set should be subtracted from which numbers in the first set? You cannot just do it in order, because there is no real "order" in a set/table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2008 at 11:50 am
Here is what I'm trying to accomplish.
I'm creating a golf scorecard. The golf course has pars on each hole and I would like to calculate the difference against a players score for each hole.
Table1:
Column Name / Data Type
CourseParID (pk) / int
CourseID / int
Hole1 / smallint
Hole2 / smallint
Hole3 / smallint
Table2:
Column Name/ Data Type
ScoreCardID (pk) / int
GolferID / int
CourseID / int
Hole1 / smallint
Hole2 / smallint
Hole3 / smallint
Thank you again. I'm open to any suggestions.
April 16, 2008 at 12:31 pm
Select GolferID, t2.CourseID
, t2.Hole1 - t1.Hole1
, t2.Hole2 - t1.Hole2
, t2.Hole3 - t1.Hole3
From Table2 t2
Join Table1 t1 ON (t1.CourseID-t2.CourseID)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2008 at 12:53 pm
rbarryyoung thank you for your help.
I copied the query statement and received the following error message.
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Also Table1 has a CourseParID would this alter the query statement?
Thanks.
April 16, 2008 at 12:58 pm
gtan018 (4/16/2008)
rbarryyoung thank you for your help.I copied the query statement and received the following error message.
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Also Table1 has a CourseParID would this alter the query statement?
Thanks.
Barry has a typo in the ON clause...
should be:
ON (t1.CourseID=t2.CourseID)
As far as how the two tables are supposed to relate to each other - you'd have to tell us. (I just saw your DDL description above). I would have assumed like Barry did, that the courseID's should match, but that might not be true.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 1:08 pm
Made the correction. Now I get:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'CourseID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'CourseID'.
April 16, 2008 at 1:19 pm
Post your query, maybe we will see what's wrong.
😎
April 16, 2008 at 1:23 pm
Sure.
SELECT GolferID, t2.CourseID
, t2.Hole1 - t1.Hole1
, t2.Hole2 - t1.Hole2
, t2.Hole3 - t1.Hole3
FROM ScoreCard t2
JOIN CoursePar t1 ON (t1.CourseID=t2.CourseID)
April 16, 2008 at 1:27 pm
Try each of these statements by themselves - which one gives you the error?
select courseID from ScoreCard
or
select courseID from CoursePar
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 1:34 pm
The first statement has the error:
(select courseID from ScoreCard) receives an error of Invalid column Name 'courseID'.
The second statement gives me the courseID value.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply