How to build this SQL query

Factual Questions
1

I have two tables:

Requests:

  • receivedTime
  • processedTime
  • requestID
  • requestType

Responses:

  • sentTime
  • requestID

There may be more than one response per request.

I want to compute the average processing time and sending time, grouped by request type, in a single query.

A simple query like:

SELECT avg(processedTime - receivedTime) AS procTime, avg (sentTime - processedTime) AS sendTime FROM requests, responses WHERE requests.requestID = responses.requestID GROUP by requestType

does not work, because the processing time average ends up weighted by how many responses there are to a given request.

Something like

SELECT avg(processedTime - receivedTime), avg(SELECT avg(res.sentTime - req.processedTime) FROM responses res WHERE res.requestID = req.requestID) FROM requests req GROUP BY requestType

does not work either, because the database complains about req.requestID not being part of an aggregate function.

Any ideas? I have gotten this to work by using two separate queries and manually matching up the results, but I’m curious about whether it could be done in one.

2

If your database supports COUNT DISTINCT:
SELECT sum(processedTime - receivedTime) AS totalprocTime, sum (sentTime - processedTime) AS totalsendTime, count(distinct requests.requestID) reqCount FROM requests, responses WHERE requests.requestID = responses.requestID GROUP by requestType

Then work out averages as total / reqCount for each.

3

But the problem is, I want to know the average time that it took to send each individual response, not the average time it took to send all the responses.

With that query, the sending time average would be same for 1 response that took 10 seconds, and 5 responses that took 2 seconds each.

4

You said you had two queries written that worked. Have you tried joining them?

Something like:

Select a.requestType, a.procTime, b.sendTime
from
(QUERY THAT RETURNS procTime) A

INNER JOIN (QUERY THAT RETURNS sendTime ) B

on a.requestType = b.requestType
where WHATEVER CRITERIA

you might need a different type of join.

this assumes that you what works in SQL Server 2005 will work in whatever you are using. And that each of the subqueries returns a two field data set. You might need to tweak the syntax a bit

5

Okay, I think I see the issue.

Processing time is only derived from request data, and should NOT be weighted differently for multiple responses. But send time should weight each response equally.

That’s a humdinger. Let me think about it.

Sample data set that I had to type out to understand, including it for any future benefit to the rest of the thread:



receivedTime processedTime requestID requestType
============ ============= ========= ===========
9:00         9:01          1         GET
9:05         9:07          2         POST

sentTime  requestID
========  =========
9:03      1
9:04      1
9:08      2
6

How did you get the data to line up like that. I typed up some stuff and when I previewed, all my formatting went to crap.

7

Put the text in a <code>blah blah blah</code> block and it looks like this:


blah blah blah

I’ve been fighting Oracle Reports all day so I don’t feel like any more database stuff right now. I’ll let someone else sort Absolute’s SQL.

8

I’m not sure that this would count as a single query, and I don’t have a SQL server handy to test this on at the moment, but would something like this work?



SELECT requestType, procTime, sendTime
FROM
    (SELECT requestType, avg(processedTime - receivedTime) AS procTime
     FROM requests
    GROUP BY requestType) ProcData
INNER JOIN
    (SELECT requestType, avg (sentTime - processedTime) AS sendTime
     FROM responses
     INNER JOIN requests ON responses.requestID = requests.requestID
     GROUP BY requestType) SentData
ON ProcData.requestType = SentData.requestType
9

This is essentially what i said above, except you actually wrote out the 2 subqueries. Assuming each subquery returns the correct results, your query should work. The only thing is that you need to alias requestType after select, or you will get an error (becuase it shows up in both subqueries, it won’t know which one to get). Just replace “requestType” with “ProcData.requestType”.

I thik the OP just wanted one thing he could execute and get all his results. This certanly fits the bill, in my opinion.

10

I went ahead and tried it! Sofaspud had the answer. (Queries run in an Oracle database)


SQL> select request_id, request_type, received_time, processed_time
  2   from requests
  3   order by request_id, received_time, processed_time ;

REQUEST_ID REQUEST_TY RECEI PROCE
---------- ---------- ----- -----
         1 ABC        01:00 01:15
         2 DEF        02:00 02:20
         3 GHI        03:00 03:25
         4 JKL        04:00 04:35
         5 JKL        05:00 05:40

SQL> select request_id, sent_time
  2   from responses ;

REQUEST_ID SENT_
---------- -----
         2 02:30
         3 03:40
         3 03:45
         4 04:45
         5 05:50
         5 05:55

6 ligne(s) sélectionnée(s).

SQL> select
  2     c.request_type,
  3     c.avg_processing_time * 24 as avg_ptime_in_hrs,
  4     d.avg_sending_time * 24 as avg_stime_in_hrs
  5  from
  6  (select request_type, avg (processed_time - received_time) as avg_processing_time
  7   from requests
  8   group by request_type) c
  9  inner join
 10  (select b.request_type, avg (a.sent_time - b.processed_time) as avg_sending_time
 11    from responses a inner join requests b using (request_id)
 12    group by b.request_type) d
 13  on c.request_type = d.request_type ;

REQUEST_TY AVG_PTIME_IN_HRS AVG_STIME_IN_HRS
---------- ---------------- ----------------
DEF                   0,333            0,167
GHI                   0,417            0,292
JKL                   0,625            0,194

SQL>
11

Great, thank you guys - I really should make the effort to learn SQL a bit better.

12

No problem. Glad we could help.