Subtracting Value

• Shadab Shah

  SSCarpal Tunnel

  Points: 4759

  More actions

  September 5, 2012 at 7:42 am

  #266333

  I have 2 queries asselect 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-120898

  SSC-Dedicated

  Points: 33944

  More actions

  September 5, 2012 at 7:54 am

  #1533824

  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

  Hall of Fame

  Points: 3178

  More actions

  September 5, 2012 at 8:10 am

  #1533835

  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

  SSCrazy Eights

  Points: 8040

  More actions

  September 5, 2012 at 8:25 am

  #1533855

  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 https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
  My blog: http://uksqldba.blogspot.com
  Visit http://www.DerekColley.co.uk to find out more about me.

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  September 5, 2012 at 7:15 pm

  #1534203

  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![/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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 7, 2012 at 6:08 pm

  #1535346

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 7, 2012 at 6:47 pm

  #1535356

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 7, 2012 at 6:58 pm

  #1535358

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 7, 2012 at 6:59 pm

  #1535359

  Shadab Shah (9/5/2012)

  ---

  I have 2 queries asselect 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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  September 7, 2012 at 8:12 pm

  #1535366

  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![/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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 8, 2012 at 5:04 pm

  #1535427

  Look at the original code the op posted. Why would you BGROUP BY ID and take an avarage if there wasn't going to be more than one of the samme 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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Dwain Camps

  SSC Guru

  Points: 86908

  More actions

  September 9, 2012 at 6:21 pm

  #1535516

  Jeff Moden (9/8/2012)

  ---

  Look at the original code the op posted. Why would you BGROUP BY ID and take an avarage if there wasn't going to be more than one of the samme ID?

  Oh my! How I hate hidden requirements.

  Perhaps I can come up with something different but close to yours in speed:

  SELECT ID=id, T1MinusT2=SUM(a)

  FROM (

  SELECT id, a=AVG(a)

  FROM @Table1

  GROUP BY id

  UNION ALL

  SELECT id, a=-AVG(b)

  FROM @Table2

  GROUP BY id

  ) a

  GROUP BY id

  Note I did say "close" and not "faster." It is not the latter but any time I can come that close to one of your solutions I feel pretty good.

  ---

  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

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  September 9, 2012 at 8:37 pm

  #1535524

  I guess we need to hear from the OP to determine whether or not the "hidden requirements" I perceived are real or not.

  And thanks for the compliment, Dwain. :blush:

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)