Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to divide by a subquery Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, February 20, 2014 3:16 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:38 AM Points: 40, Visits: 148
 `DROP TABLE #testCREATE TABLE #test(ndx int,test int,duration int, Id int,layer int)insert into #test values ( 1,0,60,11,1);insert into #test values ( 2,50,60,11,1);insert into #test values ( 3,50,60,11,1);insert into #test values ( 4,0,90,11,2);insert into #test values ( 5,75,90,11,2);insert into #test values ( 6,0,90,11,2);select * from #test`I want:SELECT ID,layer SUM(test*duration) AS Result FROM test WHERE test > 1/SELECT SUM(test) FROM testGROUP BY ID.layerSo calculation should be 50*60+50*60/60+60+6075*90/90+90+90ResultID layer Result11 1 33.33311 2 25
Post #1543382
 Posted Thursday, February 20, 2014 3:35 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 5:18 AM Points: 2,790, Visits: 2,740
 Hi Brett,is this:-`SELECT ID,layer SUM(test*duration) AS Result FROM test WHERE test >1/SELECT SUM(test) FROM testGROUP BY ID.layer`what you've tried so far? Only reason I'm asking is I found`SELECT *FROM ( SELECT ID , layer , SUM(test * duration) AS Result FROM #LocalTempTable WHERE test > 1 GROUP BY ID , layer ) AS A INNER JOIN ( SELECT id ,layer, SUM(test) Result1 FROM #LocalTempTable GROUP BY ID , layer ) AS B ON a.ID = b.id AND a.layer = b.layer`Might be a better starting point (I might be wrong though). Try working with the above and if you get stuck from there. If you do, let us know how far you got and the problems / errors you're getting.CheersETA - you also won't get 33.333 as a result when all the datatypes are set to int -------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Post #1543389
 Posted Thursday, February 20, 2014 4:22 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:38 AM Points: 40, Visits: 148
 Thanks Stuart. I have edited my original post as I mixed up my temp table name with my SQL query so far. Sorry for that.Basically I want to multiply test by duration, sum all of these results, then divide by the sum of duration. But I need to group the results into ID and layer (ID and layer define one piece of equipment.)This may explain:SELECT ID,layer,SUM(test*duration)/SUM(duration)FROM test WHERE ID = 11 and layer = 1GROUP BY ID, layerSELECT ID,layer,SUM(test*duration)/SUM(duration)FROM test WHERE ID = 11 and layer = 2GROUP BY ID, layeretc WHERE ID ==11 and layer = 3...........and so on But of course I want the result in one table for every combination of [ID] and [layer] that will exist in the real large table
Post #1543400
 Posted Thursday, February 20, 2014 4:35 AM This worked for the OP
 SSC Rookie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:38 AM Points: 40, Visits: 148
 Think I got it.`SELECT ID, layer, result/result1FROM ( SELECT ID , layer , SUM(test * duration) AS Result FROM #test WHERE test > 1 GROUP BY ID , layer ) AS A INNER JOIN ( SELECT id as i ,layer as l , SUM(duration) Result1 FROM #test GROUP BY ID , layer ) AS B ON a.ID = b.i AND a.layer = b.l`Thanks a lot Stuart, you got me 99% there Had to rename table B column to eliminate ambiguity problems.Thank you.
Post #1543407
 Posted Thursday, February 20, 2014 5:43 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 5:18 AM Points: 2,790, Visits: 2,740
 You're welcome. You will still have to resolve the lack of decimals places you want to display.Quick and dirty way of doing it - see example below :-`DECLARE @A INT, @B INT SET @A = 22 SET @B = 7 SELECT @A/@B , @A/(@B *1.00)`Best way of doing it is to use an appropriate datatype in the table you create. -------------------------------Posting Data Etiquette - Jeff Moden Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx
Post #1543434
 Posted Thursday, February 20, 2014 5:59 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:38 AM Points: 40, Visits: 148
 Changing the column datatype to float produced the desired effect. Was going to CAST AS FLOAT etc. Never thought of this solution.Thanks yet again
Post #1543442
 Posted Thursday, February 20, 2014 7:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 10:39 AM Points: 119, Visits: 620
 You could also cast the value as decimal :)
Post #1543482
 Posted Thursday, February 20, 2014 8:30 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:21 PM Points: 2,540, Visits: 5,393
 You don't need to scan the table twice `SELECT Id, layer, SUM(CASE WHEN test > 1 THEN test * duration END) / SUM(duration * 1.0)FROM #testGROUP BY ID, layer` Luis C.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1543542
 Posted Thursday, February 20, 2014 9:00 AM
 SSC Rookie Group: General Forum Members Last Login: Thursday, February 20, 2014 10:38 AM Points: 40, Visits: 148
 Thanks Lius, it does bring the same results.I'm guessing this method would be more efficient when dealing with large tables (as this query will have to do)?
Post #1543560
 Posted Thursday, February 20, 2014 9:31 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 9:21 PM Points: 2,540, Visits: 5,393
 It should be more efficient as it only reads the table once instead of twice.You could test it and check the differences on larger tables. Luis C.I am a great believer in luck, and I find the harder I work the more I have of it. Stephen LeacockForum Etiquette: How to post data/code on a forum to get the best help
Post #1543586

 Permissions