Return Data from multiple tables

Question

Can anyone help me to fix this SQL query.

Select c.name, c.address, c.area, sum(i.amount) from customers c 
Inner Join invoices i on c.id=i.customer_id 
Inner Join invoice_payments p on i.id = p.invoice_id 
group by c.name

Here is tables structure

Customer Table

id(int), Name(string), Area(string), Address(string)

Invoices Table

id(int), Invoice_Number (string), customer_id(int), amount(decimal)

Invoice_payments

id(int), invoice_id(int), amount(decimal)

I have two payment records of one client in invoice_payments table so it's returning double amount of that client...

How i can fix this issue

Provided query is returning following result.

Sample data and desired results would help. A db fiddle of some sort is even better. — Gordon Linoff, Sep 14 '19 at 12:17
@GordonLinoff my required result is `customer_name, address, area, unpaid_amount`. `unpaid_amount` is equal to `invoice_amount - invoice_payments_amount` — Jaa Zaib, Sep 14 '19 at 12:24
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. PS Please clarify via edits, not comments. — philipxy, Sep 14 '19 at 17:14
This is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. You don't even give an example. (Guessing: This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations.) — philipxy, Sep 14 '19 at 17:22

forpas · Accepted Answer · 2019-09-14T12:55:03.687

Calculate separately the 2 sums and join them:

select 
  ci.name, ci.address, ci.area, 
  ci.invoice_amount, cp.invoice_payments_amount 
from (
  select c.id, c.name, c.address, c.area, sum(i.amount) invoice_amount
  from customers c inner join invoices i on c.id = i.customer_id 
  group by c.id, c.name, c.address, c.area
) ci  inner join (
  select c.id, sum(p.amount) invoice_payments_amount 
  from customers c 
  inner join invoices i on c.id = i.customer_id 
  inner join invoice_payments p on i.id = p.invoice_id
  group by c.id
) cp on cp.id = ci.id

score 1 · Answer 2 · answered Sep 14 '19 at 12:19

1

You are not using the invoice_payments table at all, so remove it from the query. Also, fix the group by to include all unaggregated columns in the select:

select c.name, c.address, c.area, sum(i.amount)
from customers c join
     invoices i
     on c.id = i.customer_id 
group by c.name, c.address, c.area;

answered Sep 14 '19 at 12:19

Gordon Linoff

1,242,037
58
646
786

Actually i want to get difference of amount in invoice table and invoice_payments table. so that's why using invoice_payments – Jaa Zaib Sep 14 '19 at 12:21
2

@JaaZaib . . . that is not the question that you asked. This answers the question that you asked. If you have a different question, you should ask it as a *new* question. – Gordon Linoff Sep 14 '19 at 13:18

nbk · Answer 3 · 2019-09-14T13:12:48.487

1

This should work for you

Select c.name, c.address, c.area
, sum(i.amount) amount
,SUM(i.amount - p.amount) unpaid_amount 
from customers c 
Inner Join invoices i on c.id=i.customer_id 
Inner Join  (SELECT SUM(amount) amount,invoice_id 
             FROM invoice_payments 
             GROUP BY invoice_id) p on i.id = p.invoice_id 
group by c.name ;

The result of this is(I put my own data a in the custormer table)

name            address            area     amount  unpaid_amount
Donald Duck     helsinki stret 56  51       9000    0
Donald Trump    Trump Towers 1     52       2325    0

edited Sep 14 '19 at 13:12

answered Sep 14 '19 at 12:29

nbk

45,398
8
30
47

it's returning double amount from invoices table. – Jaa Zaib Sep 14 '19 at 12:31
Do you have some sample data? – nbk Sep 14 '19 at 12:33
i have added sample data in images – Jaa Zaib Sep 14 '19 at 12:47

Return Data from multiple tables

3 Answers3