MySQL Union not showing 2nd query result set

join;MySQLmysql-5.6subtypesunion

I am doing a union of 2 queries and trying to fetch data on two different columns, however the union is not fetching the value from 2nd result set. Can you please guide me on this.

select  *
    From  
      ( SELECT  c1.ia_device_home_carrier, avg(rf1.ia_signal_strengh_rssi) as ss
            from  rf_voice_data_verizon_sec2 rf1, device_market_config d1,
                ia_device_carrier c1
            where  rf1.ia_device_id = d1.ia_device_id
              and  rf1.ia_device_id = c1.ia_device_id
              and  rf1.ia_signal_strengh_rssi BETWEEN -150.0 AND -40.0
              and  d1.ia_project_name = 'AA'
            group by  ia_device_home_carrier
         union 
         select  c2.ia_device_home_carrier, avg(rf2.httpul) as http
            from  rf_voice_data_verizon_sec2 rf2, device_market_config d2,
                ia_device_carrier c2
            where  rf2.ia_device_id = d2.ia_device_id
              and  rf2.ia_device_id = c2.ia_device_id
              and  rf2.httpul > 0
              and  d2.ia_project_name = 'AA'
            group by  ia_device_home_carrier 
      ) x
    group by  ia_device_home_carrier

                            +------------------------+----------+
                            | ia_device_home_carrier | ss       |
                            +------------------------+----------+
                            | xyz                    | -77.8559 |
                            | abcggg                 | -69.8158 |
                            | sfg                    | -95.8884 |
                            | VZW                    | -94.3992 |
                            +------------------------+----------+

But i need a result set like this

                            +------------------------+----------+------------+
                            | ia_device_home_carrier | ss       |   httpul   |
                            +------------------------+----------+------------+
                            | xyz                    | -77.8559 |  7848.5175 |
                            | abcggg                 | -69.8158 |  5623.5231 |
                            | sfg                    | -95.8884 |  7228.5677 |
                            | VZW                    | -94.3992 |  7118.3175 |
                            +------------------------+----------+------------+

Best Answer

Since UNION lines up the columns, not the column names, you cannot directly go from what you have. However, here is one technique:

Instead of

SELECT * FROM (
    SELECT x, ss     FROM...
    UNION
    SELECT x, httpul FROM...
)

SELECT  x,
        MAX(ss) AS ss,  -- MAX() will ignore the NULL from the 'other' table
        MAX(httpul) AS httpul
FROM (
    SELECT x,         ss, NULL AS httpul  FROM ...
    UNION
    SELECT x, NULL AS ss,         httpul  FROM ...
)

SUGGESTION #1

You should store the timestamp of the minute and index it

ALTER TABLE mytable ADD COLUMN MinuteTimeStamp AFTER TimeStamp;
UPDATE mytable SET MinuteTimeStamp = TimeStamp - MOD(TimeStamp,60);
ALTER TABLE mytable ADD INDEX MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey);

Then, you can do MIN aggregation on MinuteTimeStamp.

SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp;

and use it get those records

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM mytable GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

It was tactfully pointed out that triggers would degrade performance

Perhaps doing INSERTs like this may help

INSERT INTO mytable (UniqueKey,TimeStamp,MinuteTimeStamp) VALUES
(
    uniquevalue,
    UNIX_TIMESTAMP(NOW()),
    UNIX_TIMESTAMP(NOW() - INTERVAL SECOND(NOW()) SECOND)
);

SUGGESTION #2

Since you have over 1000 columns (Ugh), perhaps a table of those minute timestamps would be better.

CREATE TABLE MinuteKeys
(
    MinuteTimeStamp INT UNSIGNED NOT NULL,
    UniqueKey INT UNSIGNED NOT NULL,
    PRIMARY KEY (UniqueKey)
    KEY MinuteTimeStamp_UniqueKey_ndx (MinuteTimeStamp,UniqueKey)
) ENGINE=MyISAM;
ALTER TABLE MinuteKeys DISABLE KEYS;
INSERT INTO MinuteKeys SELECT TimeStamp - MOD(TimeStamp,60),UniqueKey FROM mytable;
ALTER TABLE MinuteKeys ENABLE KEYS;

Then, you could use that table for the aggregation

SELECT B.* FROM
(SELECT MinuteTimeStamp,MIN(UniqueKey) UniqueKey
FROM MinuteKeys GROUP BY MinuteTimeStamp) A
INNER JOIN mytable B USING (UniqueKey);

EPILOGUE

Other suggestions are possible but you should really consider normalization of the table

See my post Too many columns in MySQL as to why

Best Answer

Related Solutions

Mysql – Query result set

Mysql – query records to fetch by time stamp

SUGGESTION #1

SUGGESTION #2

EPILOGUE

Related Question