CODEWITHSUNDEEP
mysql
Lulut
Lulut

Reputation: 109

How to use max for two tables

I want use MAX on a time field, it works but the value is "0"

This query is before I use MAX:

SELECT a.`JAM`, AVG(a.PacketLoss) AVG
FROM (
SELECT `JAM`,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT `JAM`,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a
GROUP BY a.`JAM`
LIMIT 10000

output here

output1

Then I use MAX here 

SELECT a.`JAM`, AVG(a.PacketLoss) AVG
FROM (
SELECT MAX(JAM) AS JAM,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT MAX(JAM) AS JAM,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a
GROUP BY a.`JAM`
LIMIT 10000

output2

I expected the output is last time with the value, can someone help me ? Thanks

Upvotes: 0

Views: 40

Answers (1)

Nick
Nick

Reputation: 147146

You need to take the MAX on your outer query, not the inner one. When you take it on the inner queries, MySQL gives you an indeterminate value of the non-aggregated columns e.g. VS_IPPM_FORWORD_DROPMEANS, which appears to be a 0 value for your sample data. Change your query to:

SELECT MAX(a.`JAM`), AVG(a.PacketLoss) AVG
FROM (
SELECT `JAM`,`RNC`,`IPPATH_PM` AS PHB,`VS_IPPM_FORWORD_DROPMEANS` AS PacketLoss, `VSIPPMRttMeans` AS Latency, 'IPPM'
FROM `rnc_ippm_meas`
WHERE `JAM`>= CURDATE() AND `IPPATH_PM` LIKE '%AF31%'
UNION
SELECT `JAM`,`RNC`,`IPPOOL_PM`,`VSIPPOOLIPPMForwardDrop-Means`,`VSIPPOOLIPPMRttMeans`, 'IPPOOLPM'
FROM `rnc_ippool_ippm_meas` 
WHERE `JAM`>= CURDATE() AND `IPPOOL_PM` LIKE '%AF31%') a

Note the GROUP BY is no longer required since you are taking the MAX and the AVG over the whole data set, and the LIMIT is not required as this query will only produce 1 row.

Upvotes: 2

Related Questions