[MySQL]Optimizing report monthly summary

Currently i have query that output average and max speed of the day as query that i want to optimize, like this

SELECT V_VEHICLEID, V_LICENSENO, 
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 1 AND WP_VEHICLEID = V_VEHICLEID) AS AVG1,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 2 AND WP_VEHICLEID = V_VEHICLEID) AS AVG2,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 3 AND WP_VEHICLEID = V_VEHICLEID) AS AVG3,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 4 AND WP_VEHICLEID = V_VEHICLEID) AS AVG4,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 5 AND WP_VEHICLEID = V_VEHICLEID) AS AVG5,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 6 AND WP_VEHICLEID = V_VEHICLEID) AS AVG6,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 7 AND WP_VEHICLEID = V_VEHICLEID) AS AVG7,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 8 AND WP_VEHICLEID = V_VEHICLEID) AS AVG8,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 9 AND WP_VEHICLEID = V_VEHICLEID) AS AVG9,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 10 AND WP_VEHICLEID = V_VEHICLEID) AS AVG10,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 11 AND WP_VEHICLEID = V_VEHICLEID) AS AVG11,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 12 AND WP_VEHICLEID = V_VEHICLEID) AS AVG12,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 13 AND WP_VEHICLEID = V_VEHICLEID) AS AVG13,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 14 AND WP_VEHICLEID = V_VEHICLEID) AS AVG14,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 15 AND WP_VEHICLEID = V_VEHICLEID) AS AVG15,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 16 AND WP_VEHICLEID = V_VEHICLEID) AS AVG16,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 17 AND WP_VEHICLEID = V_VEHICLEID) AS AVG17,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 18 AND WP_VEHICLEID = V_VEHICLEID) AS AVG18,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 19 AND WP_VEHICLEID = V_VEHICLEID) AS AVG19,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 20 AND WP_VEHICLEID = V_VEHICLEID) AS AVG20,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 21 AND WP_VEHICLEID = V_VEHICLEID) AS AVG21,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 22 AND WP_VEHICLEID = V_VEHICLEID) AS AVG22,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 23 AND WP_VEHICLEID = V_VEHICLEID) AS AVG23,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 24 AND WP_VEHICLEID = V_VEHICLEID) AS AVG24,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 25 AND WP_VEHICLEID = V_VEHICLEID) AS AVG25,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 26 AND WP_VEHICLEID = V_VEHICLEID) AS AVG26,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 27 AND WP_VEHICLEID = V_VEHICLEID) AS AVG27,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 28 AND WP_VEHICLEID = V_VEHICLEID) AS AVG28,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 29 AND WP_VEHICLEID = V_VEHICLEID) AS AVG29,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 30 AND WP_VEHICLEID = V_VEHICLEID) AS AVG30,
(SELECT ROUND(AVG(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 31 AND WP_VEHICLEID = V_VEHICLEID) AS AVG31,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 1 AND WP_VEHICLEID = V_VEHICLEID) AS MAX1,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 2 AND WP_VEHICLEID = V_VEHICLEID) AS MAX2,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 3 AND WP_VEHICLEID = V_VEHICLEID) AS MAX3,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 4 AND WP_VEHICLEID = V_VEHICLEID) AS MAX4,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 5 AND WP_VEHICLEID = V_VEHICLEID) AS MAX5,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 6 AND WP_VEHICLEID = V_VEHICLEID) AS MAX6,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 7 AND WP_VEHICLEID = V_VEHICLEID) AS MAX7,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 8 AND WP_VEHICLEID = V_VEHICLEID) AS MAX8,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 9 AND WP_VEHICLEID = V_VEHICLEID) AS MAX9,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 10 AND WP_VEHICLEID = V_VEHICLEID) AS MAX10,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 11 AND WP_VEHICLEID = V_VEHICLEID) AS MAX11,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 12 AND WP_VEHICLEID = V_VEHICLEID) AS MAX12,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 13 AND WP_VEHICLEID = V_VEHICLEID) AS MAX13,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 14 AND WP_VEHICLEID = V_VEHICLEID) AS MAX14,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 15 AND WP_VEHICLEID = V_VEHICLEID) AS MAX15,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 16 AND WP_VEHICLEID = V_VEHICLEID) AS MAX16,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 17 AND WP_VEHICLEID = V_VEHICLEID) AS MAX17,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 18 AND WP_VEHICLEID = V_VEHICLEID) AS MAX18,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 19 AND WP_VEHICLEID = V_VEHICLEID) AS MAX19,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 20 AND WP_VEHICLEID = V_VEHICLEID) AS MAX20,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 21 AND WP_VEHICLEID = V_VEHICLEID) AS MAX21,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 22 AND WP_VEHICLEID = V_VEHICLEID) AS MAX22,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 23 AND WP_VEHICLEID = V_VEHICLEID) AS MAX23,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 24 AND WP_VEHICLEID = V_VEHICLEID) AS MAX24,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 25 AND WP_VEHICLEID = V_VEHICLEID) AS MAX25,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 26 AND WP_VEHICLEID = V_VEHICLEID) AS MAX26,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 27 AND WP_VEHICLEID = V_VEHICLEID) AS MAX27,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 28 AND WP_VEHICLEID = V_VEHICLEID) AS MAX28,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 29 AND WP_VEHICLEID = V_VEHICLEID) AS MAX29,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 30 AND WP_VEHICLEID = V_VEHICLEID) AS MAX30,
(SELECT ROUND(MAX(WP_SPEED),2) FROM WAYPOINTS WHERE DAY(WP_DATETIME) = 31 AND WP_VEHICLEID = V_VEHICLEID) AS MAX31
FROM VEHICLES
WHERE V_USER = 'tc'

already tried this query, but i want the result table (in html) looks like query above

SELECT WP_VEHICLEID AS VEHICLE, V_LICENSENO AS REGNO, 
DATE(WP_DATETIME) AS SUM_DATE, 
ROUND(AVG(WP_SPEED),2) AS AVG_SPEED, 
ROUND(MAX(WP_SPEED),2) AS MAX_SPEED
FROM WAYPOINTS, VEHICLES
WHERE MONTH(WP_DATETIME) = 6
AND WP_ODOMETER  0
AND V_VEHICLEID = WP_VEHICLEID
AND V_USER = 'tc'
GROUP BY WP_VEHICLEID, DATE(WP_DATETIME)
ORDER BY WP_VEHICLEID, WP_DATETIME

Result table

========================================================
VEHICLE|     1     |     2     |     3     |     4     |
       -------------------------------------------------
       | AVG | MAX | AVG | MAX | AVG | MAX | AVG | MAX |
=================开发者_如何学编程=======================================
CAR A  | 10  | 30  |  90 | 160 | .......................
CAR B  | 50  | 90  |  0  | 0   | .......................
CAR C  | 15  | 20  |  10 | 10  | .......................
========================================================

any solution?

Try something like this -

SELECT
  v.V_VEHICLEID,

  ROUND(AVG(IF(DAY(WP_DATETIME) = 1, wp.WP_SPEED, NULL)), 2) AS AVG1,
  ROUND(AVG(IF(DAY(WP_DATETIME) = 2, wp.WP_SPEED, NULL)), 2) AS AVG2,
  ROUND(AVG(IF(DAY(WP_DATETIME) = 3, wp.WP_SPEED, NULL)), 2) AS AVG3,
  ...
  ROUND(MAX(IF(DAY(WP_DATETIME) = 1, wp.WP_SPEED, NULL)), 2) AS MAX1,
  ROUND(MAX(IF(DAY(WP_DATETIME) = 2, wp.WP_SPEED, NULL)), 2) AS MAX2,
  ROUND(MAX(IF(DAY(WP_DATETIME) = 3, wp.WP_SPEED, NULL)), 2) AS MAX3,
  ...
FROM VEHICLES v
  JOIN WAYPOINTS wp ON wp.WP_VEHICLEID = v.V_VEHICLEID
GROUP BY
  wp.WP_VEHICLEID

Instead of the monster query above, create a tiny table with the numbers 1-31 in it, then join to it and add some GROUP BY.

You won't have your table exactly, instead you'll have a bunch of rows with DATE, VEHICLEID. Order it by date, then by vehicleid. You'll need to loop over it building your table.