1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Avoiding doubling rows

Discussion in 'SQL PL/SQL' started by Giedrius, Nov 25, 2016.

  1. Giedrius

    Giedrius Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Vilnius
    Hello,
    to some of you this question is simple to answer. For me it took some time of watching youtube, learning in internet and still got no answer to this.

    Lets say I have data in Table_X:

    upload_2016-11-25_9-38-5.png

    And I want to get this from my excel query:
    upload_2016-11-25_9-38-56.png

    My query is this:

    SELECT
    Client_id,
    SUM ( CASE WHEN (Voucher type = 'M20')
    THEN Amount
    ELSE 0
    END) AS "Amount M20",
    SUM ( CASE WHEN (Voucher type = 'IP4')
    THEN Amount
    ELSE 0
    END) AS "Amount IP4",
    Activity
    FROM TABLE_X
    WHERE Activity IS NOT NULL

    And Output is missing IP4 calculation, because of Where condition:
    upload_2016-11-25_9-40-42.png

    But if I remove Where condition, then I get doubling of rows and this is far away from my desired output.
    upload_2016-11-25_9-41-24.png

    I would appreciate for any help.
     

    Attached Files:

  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are approaching the problem from the wrong direction, I think. Here's an example that provides the output you want (additional data was added to ensure the query worked for all data inserted):

    Code (SQL):
    SQL> CREATE TABLE table_x(
      2  client_id  NUMBER,
      3  voucher_type       varchar2(4),
      4  amount     NUMBER,
      5  activity   varchar2(10));

    TABLE created.

    SQL>
    SQL> INSERT ALL
      2  INTO table_x
      3  VALUES(1, 'IP4',15, NULL)
      4  INTO table_x
      5  VALUES(1, 'M20',100, 'US')
      6  INTO table_x
      7  VALUES(1, 'M01', 12, NULL)
      8  INTO table_x
      9  VALUES(2, 'IP4',150, 'US')
    10  INTO table_x
    11  VALUES(2, 'M20',100, NULL)
    12  INTO table_x
    13  VALUES(2, 'M01', 32, NULL)
    14  INTO table_x
    15  VALUES(3, 'IP4',150, 'UK')
    16  INTO table_x
    17  VALUES(3, 'M20',100, 'UK')
    18  INTO table_x
    19  VALUES(3, 'M01', 32, NULL)
    20  SELECT * FROM dual;

    9 ROWS created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> WITH get_m20 AS(
      2  SELECT client_Id, voucher_type, amount, activity FROM table_x WHERE voucher_type = 'M20'
      3  ),
      4  get_ip4 AS(
      5  SELECT client_Id, voucher_type, amount, activity FROM table_x WHERE voucher_type = 'IP4'
      6  ),
      7  get_activity AS(
      8  SELECT client_Id, activity FROM table_x WHERE activity IS NOT NULL
      9  )
    10  SELECT DISTINCT m.client_Id, m.amount "Amount M20", i.amount "Amount IP4", a.activity
    11  FROM get_m20 m, get_ip4 i, get_activity a
    12  WHERE m.client_id = i.client_id
    13  AND i.client_id = a.client_id
    14  ORDER BY m.client_id;

    CLIENT_ID Amount M20 Amount IP4 ACTIVITY
    ---------- ---------- ---------- ----------
             1        100         15 US
             2        100        150 US
             3        100        150 UK

    SQL>
     
  3. Giedrius

    Giedrius Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Vilnius
    Thank you, zargon!

    Wouln't it be possible to avoid using Select distinct? The data model I use is more complicated than in example and if I use Select distinct, I get some of my sums disappear.

    However, I will try the approach you suggested: I understand I should create new table with voucher_type = 'ÏP4" and then connect it to whole data model.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to test with YOUR data, but I did NOT create a new table, I used subquery factoring (the WITH clause) to return the data I needed in the form I needed from the base table. There should be no need to create additional tables in your schema.