How to calculate of sum(dr) and sum(cr) with customer name

December 13, 2023

SELECT   id,   Customer,  SUM( DEBIT ) OVER (PARTITION BY ID) as Debit,  abs(SUM( Payment ) OVER (PARTITION BY ID)) as PAYMENT  FROM
(
SELECT
      Cust_id as id,
      cust_name as customer,      
      OPENING_BLNC as Debit,
      0 as PAYMENT    FROM Customer
    union all
    select 
    c.Cust_id as id,
      c.cust_name as customer,      
      i.total_amount as Debit,
     0 as PAYMENT
    FROM Customer c,transaction t,invoice i where t.tran_id=i.inv_tran_id and c.cust_id=t.cust_id 
    union all 
    select 
    c.Cust_id as id,
      c.cust_name as customer,      
      0 as Debit,
     a.cr as PAYMENT 
    FROM Customer c,accounts a where  c.cust_id=a.cust_id 
    )

Result :

1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000
1   KARIMULLAH  68697.04    40000

Fetching same record repeatedly. Need to calculate dr and cr with customer name and opening balance

Output should be

Cus_Id  Customer        Debit           Credit
1       KARIMULLAH      68697.04        40000
2       Mr John         25020           15000

>Solution :

Don’t use analytic functions SUM(...) OVER (...). Instead, use aggregation functions SUM(...) and GROUP BY:

SELECT id,
       Customer,
       SUM( DEBIT ) as Debit,
       ABS(SUM( Payment ) as PAYMENT
FROM   (
  SELECT Cust_id as id,
         cust_name as customer,      
         OPENING_BLNC as Debit,
         0 as PAYMENT
  FROM   Customer
union all
  select c.Cust_id as id,
         c.cust_name as customer,      
         i.total_amount as Debit,
         0 as PAYMENT
  FROM   Customer c
         INNER JOIN transaction t
         ON c.cust_id=t.cust_id
         INNER JOIN invoice i
         ON t.tran_id=i.inv_tran_id
union all 
  select c.Cust_id as id,
         c.cust_name as customer,      
         0 as Debit,
         a.cr as PAYMENT 
  FROM   Customer c
         INNER JOIN accounts a
         ON c.cust_id=a.cust_id 
)
GROUP BY id, customer;