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

Question

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

see the answer of this question http://stackoverflow.com/questions/8572821/group-by-with-union-mysql-select-query — khaled_webdev, Jun 17 '13 at 10:41
You would help yourself and others better understand your query if you formatted it properly. Please do so. — Bohemian, Jun 17 '13 at 10:44
what aggregate function are you trying to apply for those group by's? GROUP BY MeterID yet you return timestamp too. (does mysql just take the first / last cursored value??) I don't like the UNION. Why not do something like WHERE (timestamp between 1369282639 AND 1369282699) OR (timestamp between 1369282739 AND 1369282799 ) If you have a lot of timestamp ranges simply make a table of ranges and join to it. — Transact Charlie, Jun 17 '13 at 10:58
If you run each part of the query separately how many results do you get for each? Can you show the 8 rows you are expecting to be returned, and the 4 rows that are in the question please? — GarethD, Jun 17 '13 at 10:58
Do you have 4 meter records for each query that match on timestamp and deviceid? — Kickstart, Jun 17 '13 at 10:59
@TransactCharlie i want multiple set of records one for each union. then i will be counting avg of some other column for all meter id's.. — Inderpal Singh, Jun 17 '13 at 11:01
@GarethD i am expecting 8 records 4 for each query as meterids are 1,2,3,4 . i expect one record for each meter id per union query — Inderpal Singh, Jun 17 '13 at 11:03

score 2 · Answer 1 · answered Jun 17 '13 at 11:04

2

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.

answered Jun 17 '13 at 11:04

Kickstart

21,403
2
21
33

nope! still returning 4 records. i want 1 row per meter id per timestamp between range – Inderpal Singh Jun 17 '13 at 11:09
That sql SHOULD return 4 rows, but with a column for each timestamp. It is which (or whether both) timestamp columns are populated for each meter id that I would like to know. – Kickstart Jun 17 '13 at 11:10

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

1 Answers1