I'm trying to calculate and list the websites in order of biggest overall reduction in response time from one time period to the next.

I don't strictly need to use a single query to do this, I can potentially run multiple queries.

``````| 1  | 1          | 93.26         | 2014-01-28 11:51:39**websites:**

| id | url                    |
| 1  | stackoverflow.com      |
| 2  | serverfault.com        |
| 3  | stackexchange.com      |
**
responses:**

| id | website_id | response_time | created_at |

| 2  | 1          | 99.46         | 2014-01-28 11:52:38
| 2  | 1          | 94.51         | 2014-01-28 11:53:38
| 2  | 1          | 104.46        | 2014-01-28 11:54:38
| 2  | 1          | 85.46         | 2014-01-28 11:56:38
| 2  | 1          | 100.00        | 2014-01-28 11:57:38
| 2  | 1          | 50.00         | 2014-01-28 11:58:38
| 2  | 2          | 100.00        | 2014-01-28 11:58:38
| 2  | 2          | 80            | 2014-01-28 11:58:38
**Ideally the result would look like:
**
| percentage_change | website_id |
| 52                | 1 |
| 20                | 2 |

I've got as far as figuring out the largest response time, but no idea how to do another query to calculate the lowest response time and then do the math, then sort the maths.

SELECT * FROM websites
LEFT JOIN (
SELECT distinct *
FROM responses
ORDER BY response_time desc) responsetable
ON websites.id=responsetable.website_id group by website_id
``````

Thanks

Hello,

I was looking over what you have and what you said you wanted to do and I have a couple of ideas on how to do it but I am a little confused by your wording.

If what you are trying to do is give the min, max or avg response times then you could use something like:

``````select websites.url,
min(responses.response_time) as 'Minimum Response',
max(responses.response_time) as 'Maximum Response',
avg(responses.response_time) as 'Average Response'
from responses
left join websites on websites.id=responsetable.website_id
group by website.url
``````

It really depends on what table you start in and I think it will run fast if you run from the responses table and join to get the names like website.url.

The other suggestion I would make is about how you are getting your response times. There is a tool out there called mtr which does a ping and traceroute together for several runs and compiles the results for you showing you where there may be any packet loss along the way. For windows there is winmtr.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.