CODEWITHSUNDEEP
sqloracle-database
Java Learner
Java Learner

Reputation: 97

Finding issue in Grouping the Data based on Payment Mode

I have table structure has depicted below.

Table 1:

   ID   Name
   1    Sar
   2    Varun
   3    Sagar

Table 2:

   Pay Id   ID  Payment Mode    Amount
   1    1   Cash                 500
   2    1   Credit Card          1000
   3    1   Debit Card           10000
   4    1   Net Banking          5000
   5    1   Gift Voucher         100

Need to get the result as specified below:

   ID   Cash    Credit Card Debit Card  Others
   1    500        1000       10000      5100

Can you help me out.

Upvotes: 0

Views: 117

Answers (1)

San
San

Reputation: 4538

Use pivot if you are using Oracle 11g

WITH table_1(PayId,ID,PaymentMode, Amount) AS 
(SELECT 1, 1, 'Cash', 500 FROM dual UNION ALL
 SELECT 2, 1, 'Credit Card', 1000 FROM dual UNION ALL
 SELECT 3, 1, 'Debit Card', 10000 FROM dual UNION ALL
 SELECT 4, 1, 'Net Banking', 5000 FROM dual UNION ALL
 SELECT 5, 1, 'Gift Voucher', 100 FROM dual),
table_2(ID, Name) AS
(SELECT 1, 'Sar' from dual UNION ALL
 SELECT 2, 'Varun' from dual UNION ALL
 SELECT 3, 'Sagar' from dual),
------------------------------
--- Preparation of data ends
------------------------------
table_final AS 
(select t1.ID, t1.amount, CASE t1.paymentmode 
                       WHEN 'Cash' THEN 'Cash'
                       WHEN 'Credit Card' THEN 'Credit Card'
                       WHEN 'Debit Card' THEN 'Debit Card'
                       ELSE 'Others'
                   END AS payemnt_mod
  FROM table_1 t1 
  JOIN table_2 t2 
    ON (t1.id = t2.id))
SELECT * 
  FROM table_final
PIVOT (SUM(amount) AS total FOR payemnt_mod IN ('Cash' AS cash, 'Credit Card' AS credit_card, 'Debit Card' AS debit_card, 'Others' AS OTHERS));

Output:

ID  CASH_TOTAL  CREDIT_CARD_TOTAL   DEBIT_CARD_TOTAL    OTHERS_TOTAL
1   500         1000                10000               5100

Your query will be

with table_final AS 
    (select t1.ID, t1.amount, CASE t1.paymentmode 
                           WHEN 'Cash' THEN 'Cash'
                           WHEN 'Credit Card' THEN 'Credit Card'
                           WHEN 'Debit Card' THEN 'Debit Card'
                           ELSE 'Others'
                       END AS payemnt_mod
      FROM table_1 t1 
      JOIN table_2 t2 
        ON (t1.id = t2.id))
    SELECT * 
      FROM table_final
    PIVOT (SUM(amount) AS total FOR payemnt_mod IN ('Cash' AS cash, 'Credit Card' AS credit_card, 'Debit Card' AS debit_card, 'Others' AS OTHERS));

Upvotes: 2

Related Questions