7 Replies Latest reply on May 31, 2020 12:46 PM by Sainaa

    select 2 columns

    Sainaa

      Hello how can I select 2 columns from this table

       

      I need 2 columns one with value amount and other one is type.

      For example:

      9000        monlthly_fee

      50000      vas

      42555      vod

      48996      others.

      If it is not monthly_fee, vas or vod it is considered others and summed.

      Also when I select them for every id there would 1~4 rows depending on how many type of value they have right?

      id1  40000 monthly_fee

      id1  25000 vas etc.

        • 1. Re: select 2 columns
          Frank Kulash

          Hi,

           

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).
          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          Sainaa wrote:

           

          Hello how can I select 2 columns from this table

           

          I need 2 columns one with value amount and other one is type.

          For example:

          9000 monlthly_fee

          50000 vas

          42555 vod

          48996 others.

          If it is not monthly_fee, vas or vod it is considered others and summed.

          Also when I select them for every id there would 1~4 rows depending on how many type of value they have right?

          id1 40000 monthly_fee

          id1 25000 vas etc.

          Make sure the desired results you post are what you want given the sample data you post.  I see the value 9000, but not 50000, 42555 or 48996.  Are they derived from the other values?

           

          This sound like an unpivoting problem.  Look at SELECT … UNPIVOT.

          • 2. Re: select 2 columns
            Mike Kutz

            Where did you get "ID1" from?

             

            Unlike Excel, databases do not store things in a particular order.

            So, if you used something like "rownum" to generate the value of "ID1", it could possibly represent what the Excel sheet says is "row 8096", not "row 1".

             

            Beyond that

            1. Use UNPIVOT to get KEY, VALUE columns
            2. Use CASE to decode column names (KEY) into the values you want
            3. Use SUM() to create the actual sum.

             

            If you want a working example, please set up an example on http://LiveSQL.oracle.com

            and post the link here.

             

            Thanks,

             

            MK

            • 3. Re: select 2 columns
              mathguy

              Mike Kutz wrote:


               

              Beyond that

              1. Use UNPIVOT to get KEY, VALUE columns
              2. Use CASE to decode column names (KEY) into the values you want
              3. Use SUM() to create the actual sum.

               

               

              That seems inefficient. I would rather sum first (before unpivoting) - the data is essentially "grouped" by ID already, so we should take advantage of that.

              • 4. Re: select 2 columns
                mathguy

                I guess you are looking for something like this. I created sample data in the WITH clause (which is not part of the solution - remove it and use your actual table and column names); I simulated just two ID's and fewer columns than you have in your real-life table. You should adapt as needed.

                 

                with

                  t_univision_invoice(id, monthly_fee, vas, vod, svod, pay_tv, look_tv, install_fee) as (

                    select 1001, 4000,    0, 2000,   0,   0, 500,   0 from dual union all

                    select 1002, 6000, 8000,    0, 400, 800,   0, 200 from dual

                  )

                select  id, charge_type, amount

                from    (

                          select id, monthly_fee, vas, vod, svod + pay_tv + look_tv + install_fee as other

                          from   t_univision_invoice

                      --  where  ...  (perhaps to select the billing MONTH)

                        )

                unpivot (amount for charge_type in (monthly_fee as 'Monthly Fee', vas as 'VAS', vod as 'VOD', other as 'Other'))

                order   by id   --  if needed

                ;

                 

                  ID CHARGE_TYPE     AMOUNT

                ---- ----------- ----------

                1001 Monthly Fee       4000

                1001 VAS                  0

                1001 VOD               2000

                1001 Other              500

                1002 Monthly Fee       6000

                1002 VAS               8000

                1002 VOD                  0

                1002 Other             1400

                 

                (Edited to add the ID column in the output - thanks Mike!)

                • 5. Re: select 2 columns
                  Mike Kutz

                  sometimes it takes me a few iterations to get it "correct".

                   

                  Your result seems to be missing the column for ID1.

                  Without a way to deterministically define each row, the OP suffers from NINO.  A variation of GIGO, NINO === Nothing In, Nothing Out.

                   

                  My $0.02

                   

                  MK

                  • 6. Re: select 2 columns
                    mathguy

                    Ugh...  Actually that is on me. I must have made the ID column hidden (an SQL*Plus feature, honored in SQL Developer too). I should have caught that before I posted.

                     

                    I'll fix that, thanks for pointing it out.

                    • 7. Re: select 2 columns
                      Sainaa

                      Yeah this is exactly what I was looking for.

                      Thank you.