Jump to content

Joining two tables as sums with individual records


684425

Recommended Posts

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 by 684425
Link to comment
Share on other sites

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 by mac_gyver
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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)

image.png.7bc76fc3e0d1934f63559573090314b1.png

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.