SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to divide by a subquery


How to divide by a subquery

Author
Message
brett.y
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
DROP TABLE #test
CREATE 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 test
GROUP BY ID.layer

So calculation should be
50*60+50*60/60+60+60
75*90/90+90+90

Result
ID layer Result
11 1 33.333
11 2 25
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5273 Visits: 4685
Hi Brett,

is this:-
SELECT ID,layer SUM(test*duration) AS Result FROM test WHERE test >1
/
SELECT SUM(test) FROM test
GROUP 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.
Cheers

ETA - 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 question

There 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
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 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 = 1
GROUP BY ID, layer

SELECT ID,layer,SUM(test*duration)/SUM(duration)
FROM test
WHERE ID = 11 and layer = 2
GROUP BY ID, layer


etc 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
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
Think I got it.

SELECT  ID, layer, result/result1
FROM ( 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 w00t
Had to rename table B column to eliminate ambiguity problems.

Thank you.
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5273 Visits: 4685
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 question

There 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
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 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
stormsentinelcammy
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 807
You could also cast the value as decimal Smile
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16284 Visits: 19059
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 #test
GROUP 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
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 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
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16284 Visits: 19059
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search