## How to divide by a subquery

 Author Message brett.y Mr or Mrs. 500 Group: General Forum Members Points: 554 Visits: 166 `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 Stuart Davies SSChampion Group: General Forum Members Points: 11187 Visits: 4865 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 brett.y Mr or Mrs. 500 Group: General Forum Members Points: 554 Visits: 166 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 brett.y Mr or Mrs. 500 Group: General Forum Members Points: 554 Visits: 166 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. Stuart Davies SSChampion Group: General Forum Members Points: 11187 Visits: 4865 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 brett.y Mr or Mrs. 500 Group: General Forum Members Points: 554 Visits: 166 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 :-D stormsentinelcammy Ten Centuries Group: General Forum Members Points: 1347 Visits: 807 You could also cast the value as decimal Luis Cazares SSC Guru Group: General Forum Members Points: 95924 Visits: 21205 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 brett.y Mr or Mrs. 500 Group: General Forum Members Points: 554 Visits: 166 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)? Luis Cazares SSC Guru Group: General Forum Members Points: 95924 Visits: 21205 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.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2