SQL query sum on 2 columns from 2 tables

Wappler General How To
#1

Morning,
really hoping there might be a SQL guru who could give me a little hand, pretty please.

I have 3 tables (simplified).

Users (id, username)
Invoices (id, user_id, due)
Payments (id, user_id, paid)

a user can have invoices with no payments. A user can have multiple payments. Payments do not correspond to invoices.

I want to show a list of users with their due, paid and balance (due-paid).

When I try this with the sum(due), sum(paid) I am getting weird things.

For instance if user.1 has 2 payments of 10.00 and 2 invoices its giving me paid of 40 (guess as its retrieving 4 rows) and doubling up

I have tried various group by options and joins but just cannot get it right :frowning:

hoping someone can point me in the right direction, I have tried may searches on google and whilst there seem a few “answers” given none seem to actually work

thanks in advance

#2

Hi,

Can’t you add a column “paid” to invoices?

A payment isn’t “paid”, the invoice is “paid”. I guess it would facilitate things (edit: sorry, I missed the part where multiple payments can account for one invoice/“payments do not correspond to invoices”)

#3

I hear what you are saying, and I wish clients paid the invoices they are sent, but our clients seem to pay what they want lol, we send an invoice of 97.98 and they send 100, or we send invoice for 50 and we get 15 then 35 etc, so it makes it very hard to correlate a payment to an invoice

the other way we want to use this bit of sql is on a per client basis, so add a where userid = nn and then be able to show a table of their invoice line items and their payments (getting same problem of duplication, i think it requires something called coallese or something like that but its losing me :frowning:

#4

Last time I had to do something similar, I performed the calculation directly in the Server Action (because I don’t know much SQL either). As long as you’re not dealing with lots of rows, it’s an ok solution - have you considered that?

#5

I did think of that, but, its a lot of rows (several hundred), I know its possible in sql, just getting myself in knots with it, I see plenty of people asking very similar requests on stack etc, but whilst answers given nobody ever says if those answers worked and when I try to rework to fit my data it doesnt :frowning: but will keep going, thanks for the suggestion but i think due to the number of rows it does need to be server side.

#6

Can you show some screenshots of what you’ve done?

I think the problem is you have the same user_id field for both tables, are you using alias on your query?

#7
#8

How are you doing your calculation…? Are you left joining in the user id field…?

Also, your payments table could do with an invoice id field to tie the payment to the invoice.

#9

yeah left outer to users_id. the user is the primary table, to get a list of all users.

i havn’t tied payments or invoices together with invoice_id in payments table etc as more often than not our clients dont pay their invoice amount but a random amount (its infuriating but hard to stop in spain).

I have actually just renamed my tables as users should be clients. so just spent all morning doing that :wink:

So, I now have:

Clients:

  • id
  • name

Invoices(

  • id
  • TotalDue
  • Client_id

Payments:

  • id
  • TotalPaid
  • Client_id

i would like it to show

Joe Bloggs - 50.00 owed (which is TotalDue - TotalPaid)
Mark Smith - 0.00 owed
Sam Jones (blank as no invoices or payments logged) (could also show 0.00 of course)

a user can have multi invoices and muiti payments.

#10

I’d recommend you have an invoice id in your payments table, that way you’d be able to group the payments by invoice ID. Once that is done, you’d be able to calculate how much outstanding balance there would be from the invoice payment minus the payments.

Without the above, you’ve no way to find which payment belongs to which invoice so you wont be able to accurately get the balances from any of them.

#11

yeah, i do get that part, just not figured quite how to make it fit the business, as, we invoice a client 90.00 they pay 100 or pay 52,54, a lot of it is due to currency fluctuations etc, but it is a pain, we would have to start to try to split payments over more than one invoice etc, which is something we may have to do witth a many to many table, not quite got my head around that at moment.

#12

this is sql as it stands

select Clients.id, Clients.Active, Data.FirstName, Data.LastName, sum(Inv.TotalAmount) as TotalDue, sum(Paid.Amount) as TotalPaid from Clients inner join ClientData as Data on Data.Client_id = Clients.id left join Invoices as Inv on Inv.Client_id = Clients.id left join Payments as Paid on Paid.Client_id = Clients.id where Clients.Admin = ? and Clients.Active = ? group by Clients.id, Clients.Active, Data.FirstName, Data.LastName order by Data.FirstName ASC, Data.LastName ASC

in my dummy data I have 1 client. he has 2 invoices 21.54 and 15.13. he has made 1 payment of 21.54.

what the query gives me is

due: 36.67

paid: 43.08

difference: -6.409999999999997

#13

Hi there @JamesJenkins…

I have a huge invoice and payment part of my app.

I’ve not read all the details of your post, but three bits of advice are:

  1. Make sure you have the appropriate fields in the appropriate database table.

  2. Do all your sums in the server action, not in the SQL. It loads faster and is more flexible.

  3. I use a lot of SQL views to combine information from different fields of different tables. Once you know how they work, they change your life forever! :tada:

Good luck!
Antony.

2 Likes
#14

Hi James,

As @Antony has mentioned in his post, you can create a view by joining Invoices and Payments tables by user_id to collate the information. The SQL statement to generate this view can include the fields like this. The syntax will differ by database for the SQL statement.

user_id, invoice_id as transaction_id, invoice_no as transaction_no, invoice_date as transaction_date, invoice_amount as transcation_amount, invoice as source
UNION ALL
user_id, payment_id as transaction_id, payment_no as transaction_no, payment_date as transaction_date, payment_amount as transcation_amount, payment as source

Then this view can be joined with user_id in the users table, which can be used in the queries to calculate totals for each user_id by source column or show in a table format on the front-end.

#15

thanks, will give some views a read up and try, i thought keeping the load on the server would have been better, but happy to try on the server action than sql, guess it would be combing some views, as another dataset i want to show is the statement of account, which would show the invoice line items (as i have an invoice table and an invoicedetails table, and the payments table, i want to repeat through the invoicedetails showing line items with the payments in there as well in date order, bit like a bank statement

thanks for the thoughts, will take a look

#16

so thanks to those who brought up views. I have created 2 views, and joined with my expression and it seems to work like a charm… well almost.

So I have my custom sql using my views, in the server connect query builder I can click the run icon and I get my data set, perfect, 1 row, 1 client, showing the sum of payments and amounts due. All great.

So, now I try to show this custom query on the front end, and no data shows, my page as far as I can tell is all correct, I had the same page showing my old results no problem from a normal query, i have checked its using the correct SC, checked the repeat region is using the correct SC query and checked the data is correct from the repeater.

I turned on notifications and got it to give last error and it just says an error occurred, but i dont get it :frowning: