September 5, 2012 at 7:42 am
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.
September 5, 2012 at 7:54 am
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/
September 5, 2012 at 8:10 am
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
September 5, 2012 at 8:25 am
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: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.
September 5, 2012 at 7:15 pm
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 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
September 7, 2012 at 6:08 pm
Post withdrawn... I made a mistake...
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2012 at 6:47 pm
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
Change is inevitable... Change for the better is not.
September 7, 2012 at 6:58 pm
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
Change is inevitable... Change for the better is not.
September 7, 2012 at 6:59 pm
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 asselect 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
Change is inevitable... Change for the better is not.
September 7, 2012 at 8:12 pm
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 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
September 8, 2012 at 5:04 pm
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
Change is inevitable... Change for the better is not.
September 9, 2012 at 6:21 pm
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 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
September 9, 2012 at 8:37 pm
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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply