Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: one for the SQL experts - dare I say TRICKY SQL!
(paulspratley_at_yahoo.co.uk) writes:
> There are 2 occassions when the user count is 1 at the start of
> Threadid 1 and when the last thread 4 is kicked off. Threadid has 2 *
> 10 (measurements) = 20 and Threadid 4 has 2 * 40 (measurements).
> Therefore 100 measurements in total. Two threads therefore average =
> 50.
It's very difficult to suggest a query, when the sample data does not really match the description, and when there is not really any any good description of the business problems.
In the sample data, the result for scriptid = 6 is from an occassion when no thread was running, not even the thread that was said to be running.
Why the two measurements for threadid = 1 should count for one user is beyond me, as when the second measurement is record, there is another thread.
At no occassion there are three threads running what I can see.
I composed this query, but it does not give the desired result.
SELECT cnt, avg(summeasurement)
FROM (SELECT cnt, threadid, summeasurement = sum(measurement)
FROM (SELECT r.measurement, r.threadid, cnt = (SELECT COUNT(*) FROM threadstart t WHERE r.startstamp BETWEEN t.startstamp AND coalesce(t.stopstamp, '99991231')) FROM result r) AS x GROUP BY threadid, cnt) AS b
-- Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspxReceived on Tue Feb 07 2006 - 17:35:56 CST