CODEWITHSUNDEEP
sql
Nikhil Rupanawar
Nikhil Rupanawar

Reputation: 4191

sql join query for multiple tables

I have following tables structure,

cust_info:

  cust_id   cust_name
  1           nikhil
  2           sam

bill_info:

 bill_id  cust_id   bill_amount 
    7         1     10000
    8         1     15000
    9         2     6000
    10        2     4000

paid_info:

   paid_id      cust_id  paid_amount  
      11         1        5000
      12         1        5000
      13         2        5000
      14         2        5000

now my output should display sum of bill made by customer and total amount paid by that customer and balance amount

output

  cust_id   total_bill   total_paid  balance
    1        25000         10000      15000
    2        10000          10000       0

where, for example, for cust_id = 2,

 total_bill= 10000 + 15000
 total_paid = 5000 + 5000
 balance = total_bill - total_paid

what is convenient way to do this in sql? any sample query?

here's what i've already tried

SELECT distinct c.cust_id
    , sum(b.bill_amount) as total_bill
    , SUM(p.paid_amt) AS totalpaid,sum(b.bill_amount) - SUM(p.paid_amt) AS balance 
FROM cust_info c 
INNER JOIN bill_info b ON c.cust_id = b.cust_id 
INNER JOIN paid_info p ON p.cust_id= b.cust_id group by p.cust_id;

Upvotes: 0

Views: 95

Answers (3)

Jackal
Jackal

Reputation: 2692

SELECT DISTINCT cust_info.cust_id,
                sum(bill_amount) AS 'total_bill' ,
                sum(paid_amount) AS 'total paid' ,
                (SUM(bill_amount) - sum(paid_amount)) AS balance
FROM cust_info
INNER JOIN bill_info ON cust_info.cust_id = bill_info.cust_id
INNER JOIN paid_info ON cust_info.cust_id = paid_info.cust_id
GROUP BY cust_info.cust_id

Upvotes: 0

T I
T I

Reputation: 9933

SELECT 
    cust_info.cust_id,
    cust_name,
    bill_amount,
    paid_amount,
    bill_amount - paid_amount AS balance
FROM cust_info
INNER JOIN (
    SELECT cust_id, SUM(bill_amount) bill_amount
    FROM bill_info
    GROUP BY cust_id
) bill_info ON bill_info.cust_id = cust_info.cust_id
INNER JOIN (
    SELECT cust_id, SUM(paid_amount) paid_amount
    FROM paid_info
    GROUP BY cust_id
) paid_info ON paid_info.cust_id = cust_info.cust_id

demo

Upvotes: 3

ivan.aguirre
ivan.aguirre

Reputation: 510

SELECT c.cust_id,
       SUM(b.total_bill),
       SUM(p.total_paid),
       SUM(c.total_bill) - SUM(p.total_paid)
FROM
    cust_info c
    LEFT JOIN bill_info b ON (c.cust_id = b.cust_id)
    LEFT JOIN paid_info p ON (c.cust_id = p.party_id)
GROUP
    BY cust_info.cust_id

Upvotes: 0

Related Questions