Joining two tables as sums with individual records

[684425]

I have two tables as shown in image, I want to join sum of records from table B based on keys in both tables to get the desired result as mentioned in third table. Please guide🙂

[684425]

In another query i will need to repeat the same and then also i will have to count records from result where BBB + CCC = 10000. Please guide me🙂

[benanamen]

1. If those are your real column names you need to change them to something meaningful. Nobody knows what BBB or YYY or CCC means.
2. You likely have a DB design problem that needs to be normalized.

Tell us about this data and what it represents.

[684425]

24 minutes ago, benanamen said:

1. If those are your real column names you need to change them to something meaningful. Nobody knows what BBB or YYY or CCC means.
2. You likely have a DB design problem that needs to be normalized.

Tell us about this data and what it represents.

Sorry sir, it is...

TableA is customers (id, invoice, downpay)

TableB is installments (id, invoices, payments)

TableC is the result of query that i want to run on both customers and installments

Sir i am not allowed to modify DB structure and i do not want to do that because it already is filled with lots of records. I can run only queries on data.

Edited August 2, 2020 by 684425

[mac_gyver]

what have your tried?

this assignment seems pretty straight-forward -

SELECT the things you want, one of them being the SUM() of the column you want to add up

FROM your two tables JOINed together ON the column that relates the data between the tables

GROUP BY the column that identifies which rows to SUM() together

and any ORDER BY column(s) that get the data into the order that you want to display it as

 

Edited August 2, 2020 by mac_gyver

[684425]

Sir, i have reached at this

SELECT
    customers.invoice,
    customers.downpay,
    SUM(installments.payments) AS paid
FROM
    customers
LEFT JOIN installments ON customers.id = installments.invoices
GROUP BY
    customers.id

Please guide me if i am wrong

[maxxd]

18 minutes ago, 684425 said:

LEFT JOIN installments ON customers.id = installments.invoices

This is suspect. I assume the 'XXX (FK)' column in your initial post is actually customerId - you'll want to join on that unless your business logic is that each customer can only have one invoice in the system, ever.

[684425]

1 hour ago, maxxd said:

This is suspect. I assume the 'XXX (FK)' column in your initial post is actually customerId - you'll want to join on that unless your business logic is that each customer can only have one invoice in the system, ever.

1. You are right sir. this column should be named as customerid but it is named as invoice and i am not allowed to change it.

2. There is only one duplicate in history data but that account is closed. After that every customer is assigned a unique invoice.

[maxxd]

8 minutes ago, 684425 said:

After that every customer is assigned a unique invoice

Sure, but how do you connect the invoice to the customer? Again, I assume that your customers can have more than one invoice throughout all of history, so that's the join you need - you need to link customer ID to customer ID between the tables, not customer ID to invoice ID.

[684425]

4 hours ago, maxxd said:

Sure, but how do you connect the invoice to the customer? Again, I assume that your customers can have more than one invoice throughout all of history, so that's the join you need - you need to link customer ID to customer ID between the tables, not customer ID to invoice ID.

Agree with you sir but the problem here is that DB is already created and filled with data. I tried to move data to a new DB and i was discussing the matter here. In the middle i was stopped from doing this, so i have to continue with the old one.

Here in my first post i pasted a sample of only those fields on which i wanted to run queries like as query total amounts paid by each customer, find number of customers whose total payments are (1. less than given input, 2. equal to given input, 3. greater than given input)

What i am facing here is, old DB is not normalized, also (dynamic?) values calculated from other values are stored in tables (the exact term for calculated is slipped from my mind)

The image in this reply is corrected one (in my opinion, not in the DB i am working on)

Here invoices.id is primary key in invoices table and installments.invoiceid id foreign key in installments table. i created these two tables (invoices and installments) and inserted exactly same data, then ran the following query and it worked. I just wanted to confirm that, is this query correct for any situation limited to these two tables? or is there something that i missed?

SELECT
    invoices.invoice,
    invoices.downpay,
    SUM(installments.payments) AS paid
FROM
    invoices
LEFT JOIN installments ON invoices.id = installments.invoiceid
GROUP BY
    invoices.id

I also want to apologize. I am very sorry for creating this mess and confusing all of you. I hope that i will be forgiven by all the contributors who tried to help me here.