SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Subtracting Value


Subtracting Value

Author
Message
Shadab Shah
Shadab Shah
SSC-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 798
I have 2 queries as
select id,sum(a)/count(id) as e from table1 group by id

and another query as
select id,sum(b)/count(id) as ffrom table2 group by id

.Now i want to perform e - f. Could somebody help me please.
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3701 Visits: 6512
If I understood what you want, you can join tables by id and then do it (see my example bellow), but I do have to admit that I'm not sure that I understood you, and that I think that it will be better if you'll explain what you need and add a script that creates the table, add test data and show what you expect to get.


select t1.id, sum(t1.a)/count(t1.id) - sum(t2.b)/count(t2.id)
from t1 inner join t2 on t1.id = t2.id
group by t1.id



Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Sarah Wagner
Sarah Wagner
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1632 Visits: 715
This is probably over kill, but if you might not have the same id in both tables this will give you all:

select a.id, a.e - a.f as Diff
FROM (select id, sum(a)/count(id) as e, 0 as f from table1 group by id
union all
select id, 0 as e, sum(b)/count(id) as f from table2 group by id) as a
group by a.id
derek.colley
derek.colley
SSC Eights!
SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)SSC Eights! (880 reputation)

Group: General Forum Members
Points: 880 Visits: 603
Assuming a structure, at its simplest, as:


CREATE TABLE table1 (id INT, a INT)
CREATE TABLE table2 (id INT, b INT)
INSERT INTO table1
SELECT 1,1 UNION SELECT 2,2 UNION SELECT 3,3
INSERT INTO table2
SELECT 1,1 UNION SELECT 2,2 UNION SELECT 3,3



Then:


CREATE TABLE table3 (id INT IDENTITY(1,1), e INT, f INT,
divided AS ((e/f) * CAST(1 AS DECIMAL(10,2)) ) )
INSERT INTO table3 (e, f)
SELECT (SUM(t1.a) / COUNT(t1.id)) [e],
(SUM(t2.b) / COUNT(t2.id)) [f]
FROM table1 t1, table2 t2



The 'divided' column in table3 will be your result. You can use temporary tables instead of permanent ones for simplicity.

Tested OK.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7401 Visits: 6431
Last time I checked my statistics book SUM/COUNT = AVG, so allowing for the fact that some IDs may not exist in one table or the other, I will propose this:


DECLARE @table1 TABLE (id INT, a INT)
DECLARE @table2 TABLE (id INT, b INT)
INSERT INTO @table1
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9
INSERT INTO @table2
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4

SELECT CASE WHEN a.id IS NULL THEN b.id ELSE a.id END
,CASE WHEN AVG(a) IS NULL THEN 0 ELSE AVG(a) END -
CASE WHEN AVG(b) IS NULL THEN 0 ELSE AVG(b) END
FROM @table1 a
FULL OUTER JOIN @table2 b ON a.id = b.id
GROUP BY CASE WHEN a.id IS NULL THEN b.id ELSE a.id END





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87410 Visits: 41113
Post withdrawn... I made a mistake...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87410 Visits: 41113
Dwain,

You made the same mistake that I originally made. Try your code on the following test data and marvel at the pain of an accidental CROSS JOIN.


DECLARE @table1 TABLE (id INT, a INT)
DECLARE @table2 TABLE (id INT, b INT)
INSERT INTO @table1
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @table2
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2



The problem is when you have more than 1 of the same ID. The t1.ID=t2.ID makes a many-to-many join (CROSS JOIN) between identical ID's.

To fix the problem, we have to calculate the averages first and then do a join on the results. Here's one way...


WITH
cteT1 AS (SELECT ID, T1Avg = AVG(a) FROM @Table1 GROUP BY ID),
cteT2 AS (SELECT ID, T2Avg = AVG(b) FROM @Table2 GROUP BY ID)
SELECT ID = ISNULL(t1.ID,t2.ID),
T1MinusT2 = ISNULL(t1.T1Avg,0) - ISNULL(t2.T2Avg,0)
FROM cteT1 t1
FULL JOIN cteT2 t2
ON t1.ID = t2.ID
;



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87410 Visits: 41113
derek.colley (9/5/2012)
Assuming a structure, at its simplest, as:


CREATE TABLE table1 (id INT, a INT)
CREATE TABLE table2 (id INT, b INT)
INSERT INTO table1
SELECT 1,1 UNION SELECT 2,2 UNION SELECT 3,3
INSERT INTO table2
SELECT 1,1 UNION SELECT 2,2 UNION SELECT 3,3



Then:


CREATE TABLE table3 (id INT IDENTITY(1,1), e INT, f INT,
divided AS ((e/f) * CAST(1 AS DECIMAL(10,2)) ) )
INSERT INTO table3 (e, f)
SELECT (SUM(t1.a) / COUNT(t1.id)) [e],
(SUM(t2.b) / COUNT(t2.id)) [f]
FROM table1 t1, table2 t2



The 'divided' column in table3 will be your result. You can use temporary tables instead of permanent ones for simplicity.

Tested OK.


Derek,

Try it with the test data I posted and see how long it takes because of the CROSS JOIN and see that it still only comes up with one row even with your data.

Also, the OP wanted the difference between the two averages.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87410 Visits: 41113
Shadab Shah (9/5/2012)
I have 2 queries as
select id,sum(a)/count(id) as e from table1 group by id

and another query as
select id,sum(b)/count(id) as ffrom table2 group by id

.Now i want to perform e - f. Could somebody help me please.


Would you clarify, please? I'm assuming that you want e-f as joined on ID, correct?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7401 Visits: 6431
Jeff Moden (9/7/2012)
Dwain,

You made the same mistake that I originally made. Try your code on the following test data and marvel at the pain of an accidental CROSS JOIN.


DECLARE @table1 TABLE (id INT, a INT)
DECLARE @table2 TABLE (id INT, b INT)
INSERT INTO @table1
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL
SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1
INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @table2
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL
SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2
INSERT INTO @Table2 SELECT * FROM @Table2



The problem is when you have more than 1 of the same ID. The t1.ID=t2.ID makes a many-to-many join (CROSS JOIN) between identical ID's.

To fix the problem, we have to calculate the averages first and then do a join on the results. Here's one way...


WITH
cteT1 AS (SELECT ID, T1Avg = AVG(a) FROM @Table1 GROUP BY ID),
cteT2 AS (SELECT ID, T2Avg = AVG(b) FROM @Table2 GROUP BY ID)
SELECT ID = ISNULL(t1.ID,t2.ID),
T1MinusT2 = ISNULL(t1.T1Avg,0) - ISNULL(t2.T2Avg,0)
FROM cteT1 t1
FULL JOIN cteT2 t2
ON t1.ID = t2.ID
;



Duplicate IDs? Who said anything about duplicate IDs?

You are correct though that I hadn't handled that case properly, but my assumption was that you'd be joining on unique IDs.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search