0

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 |
                            +------------------------+----------+------------+
  • Union adds rows, not columns, you probably want a join instead. – jkavalik Mar 2 '16 at 14:00
1

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...
)

do

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 ...
)
0

Try this:

SELECT  SS.IA_DEVICE_HOME_CARRIER ,SS.SS ,HTTP.HTTP
    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
      ) SS
    INNER JOIN  
      ( 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
      ) HTTP ON SS.IA_DEVICE_HOME_CARRIER=HTTP.IA_DEVICE_HOME_CARRIER
  • Thank you. I did try with a join, but forgot to put the ON Clause at the end. Any ways thanks – aalney Mar 4 '16 at 7:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.