Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Subtracting Value Expand / Collapse
Author
Message
Posted Wednesday, September 5, 2012 7:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:08 AM
Points: 268, Visits: 670
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.
Post #1354542
Posted Wednesday, September 5, 2012 7:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 23, 2014 5:28 AM
Points: 2,114, Visits: 5,502
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/
Post #1354549
Posted Wednesday, September 5, 2012 8:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 23, 2014 9:13 AM
Points: 1,329, Visits: 620
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

Post #1354561
Posted Wednesday, September 5, 2012 8:25 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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.
Post #1354583
Posted Wednesday, September 5, 2012 7:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1354980
Posted Friday, September 7, 2012 6:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356311
Posted Friday, September 7, 2012 6:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356322
Posted Friday, September 7, 2012 6:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356324
Posted Friday, September 7, 2012 6:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:25 PM
Points: 35,372, Visits: 31,925
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356325
Posted Friday, September 7, 2012 8:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1356332
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse