February 20, 2014 at 3:16 am
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
February 20, 2014 at 3:35 am
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 [/url]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
February 20, 2014 at 4:22 am
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
February 20, 2014 at 4:35 am
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.
February 20, 2014 at 5:43 am
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-2 INT
SET @a = 22
SET @b-2 = 7
Best way of doing it is to use an appropriate datatype in the table you create.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]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
February 20, 2014 at 5:59 am
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 😀
February 20, 2014 at 7:24 am
You could also cast the value as decimal 🙂
February 20, 2014 at 8:30 am
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
February 20, 2014 at 9:00 am
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)?
February 20, 2014 at 9:31 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply