CODEWITHSUNDEEP
mysqlsqldatabase
Inderpal Singh
Inderpal Singh

Reputation: 270

Union doesn't work when used with group by in mysql? whats wrong?

i have written a query like

(select meterID, timestamp from meter_data 
          where timestamp between 1369282639 AND 1369282699 
                AND deviceID = "1" 
                        GROUP BY meterID) 
UNION 

(select meterID, timestamp from meter_data 
          where timestamp between 1369282739 AND 1369282799 
                AND deviceID = "1" 
                         GROUP BY meterID);

i expect 2 set of data from it like - i have 4 distinct meterIDs in my table so it should return me 8 records yet its returning only 4 records

Upvotes: 0

Views: 171

Answers (1)

Kickstart
Kickstart

Reputation: 21513

To help understand the data can yu try the following

SELECT a.meterID, MAX(b.timestamp), MAX(c.timestamp)
FROM meter_data a
LEFT OUTER JOIN meter_data b ON a.meterID = b.meterID AND b.timestamp between 1369282639 AND 1369282699 AND b.deviceID = "1" 
LEFT OUTER JOIN meter_data c ON a.meterID = c.meterID AND c.timestamp between 1369282739 AND 1369282799 AND c.deviceID = "1" 
GROUP BY a.meterID

This should return one row per meter id and the max timestamp within each range for that meter id.

Upvotes: 2

Related Questions