Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to divide by a subquery Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 3:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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
Post #1543382
Posted Thursday, February 20, 2014 3:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 3,060, Visits: 3,246
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
Post #1543389
Posted Thursday, February 20, 2014 4:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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
Post #1543400
Posted Thursday, February 20, 2014 4:35 AM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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
Had to rename table B column to eliminate ambiguity problems.

Thank you.
Post #1543407
Posted Thursday, February 20, 2014 5:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 3,060, Visits: 3,246
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
Post #1543434
Posted Thursday, February 20, 2014 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:06 AM
Points: 123, Visits: 682
You could also cast the value as decimal :)
Post #1543482
Posted Thursday, February 20, 2014 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
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.
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?

Forum 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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 6:58 AM
Points: 40, Visits: 152
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 3,545, Visits: 7,653
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1543586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse