mysql - How to get avg time taken by grouping a particular column using sql? -

I have a table like this for which I am getting the total number of a specific call_count Datacenter for Model_ID in PHX , slc and SJC data meta_date.

  machine_name hour_of_day sec_of_hour meta_date client_id MODEL_ID CALL_COUNT average median datacenter machine 13 168 2014-07-30 00:00:00 Test13N 65 100 38,6382 5 PHX machineâ 13 170 2014-07-30 00 : 00: 00 Test13N 65 200 34382 4 PHX machineB 13 168 2014-07-30 00:00:00 Test 13 N 65 200 33.62 3 SLC Machine B 13 170 2014-07-30 00:00:00 Test 13 N 65 300 32 60 2 SLC Machine C 13 168 2014-07-30 00:00:00 Test 13 N 65 400 31.20 6 SJC Machine C 13 170 2014-07-30 00:00:00 Test 13 N 65 500 39.82 8 SJC  

then with the following question -

  select client_id, model_id, sum (case when datacenter = 'PHX' then call_count END) phx, sum (case When datascent = 'SLC' So call_count END) SLC, sum (case when Datacenter = 'SJC' So CALL_COUNT end) Model B SJC where meta_date = CURDATE () - 1 group client_id, client_id, MODEL_ID ordered by MODEL_ID;  

For ME Model_ID 65, this means 300 calls were made from PHX, 500 calls were made from SLC and 900 calls were made from SGC datacenter.

Issue Statement: -

What should I do, I have to show average time taken in PHX, SLC and SJC datacenters for all calls of a particular Model_ID. Similarly for PHX, SLC and SJC datacenters for median as well.

The mean mean time is taken by PHX datacenter taken by 300 calls in PHX datacenter and 300 calls in median time. Similarly for SLC and SJC

And I'm thinking about using the weighted average here. Since with the above table for the machineâ, 100 calls took an average of 34.382 ms in 170 seconds of hours taken for 168 seconds of call and 38 hours in an average PHX datacenter of 38.6382 MS.

So I am thinking of calculating the average weighted average of the PHX data center using

  (100 * 38.6382 + 200 * 34.382) / ( 100 + 200)  

In this end I need to show results like this -

  client_id MODEL_ID PHX SLC SJC phx_avg phx_median slc_avg slc_median sjc_avg sjc_median test13n 65 300 from 500 900  

adjust the above formula in my current SQL how do I so I 300 calls were taken by 300 calls in Piacaks average time Piacaks the May be an average calculation of time taken by. Similarly for SLC and SJC I am working with mysql database

calculating the average time Your idea of ​​using a weighted average for is correct.

This query () fixes the work.

 Select  yoga (case when datascentor = 'PHX' then CALL_COUNT end) PHX, sum (case when datacenter = 'PHX' then callcount * average ADD) / zodiac ( case when datacenter = 'Piacaks so call_count END) phx_avg,  

you do not have enough information to calculate your average number; That requires the presence of expansion records that went into the mediators shown in your table. You can calculate in any way the counterfeit, but if your traffic engineers base their work on a simulated median calculation, then your company will apologize. Do not do this!