There are two tables:

  1. bills (each with a unique ID and the amount you need to pay).

  2. payments on these invoices, carried out with indication of the IDI invoice and the amount of payment (each payment has its ID, one invoice may have several payments).

Question: how to display a list of invoices, which will show the amount of all payments on this account and how much is left to pay (that is, the difference between the amount of the invoice and the amount of all payments)?

I tried to do this:

global $wpdb; $rates_sql = $wpdb->get_results("SELECT * FROM factures LEFT JOIN payment ON payment.facture_date=factures.letter_id AND payment.facture_number=factures.id_number WHERE '$date'<='$date_now'"); } foreach ($rates_sql as $result) { $refuseInter=$result->refuse_inter; $refusePay=$result->refuse_pay; $prepayment=$result->prepayment; $sendCourier=$result->send_courier; $expDate=$result->exp_date; $firstIntervDate= $result->first_interv; $date = date('Ym-d', strtotime($firstIntervDate. "+ ".$expDate."days")); $date_now = date("Ymd"); $payment=$result->check_sum; if ($result->tarif_ttc - $results->summary!=0) { ?> 

 <td><?php echo $result->cliend_id; ?></td> <td><?php echo $result->id_number; ?></td> <td><?php echo $result->name; ?></td> <td><?php echo $result->add_code; ?></td> <td><?php echo $results->summary; ?></td> <td><?php echo $result->tarif_ttc - $results->summary ?></td> <td><?php if ($refuseInter == 0) { ?>

but in this case, the table repeats the invoices in which two or more prepayments. help, please, sum up these payments and link them to one account.

table structures:

The factures table has columns:

 letter_id, id_number, cliend_id, task, metier, check_num, conceil, nex_intervent, creation_date, name, add_num, add_street, add_code, add_city, hotte_number, tarif_ht, tariv_tva, tarif_ttc, first_interv, exp_date, refuse_inter, refuse_pay, prepayment, send_courier

payment table has columns:

 id_payment, ID, name, facture_date, facture_number, zip-code, contrat_by_courier, check_get_pay, cb_got, virement_got, prelevement_got, lcr_got, check_number, payment_date, send_facture_by_courier, send_facture_by_mail, send_attestation_by_courier, send_attestation_by_mail, check_sum, bank_name, check_num, date_cashed
  • will help you in the query use SUM (column). You can find detailed information in the mysql documentation - ArchDemon
  • one
    Without the table structure, it is difficult to help you. - Nikolaj Sarry 6:49 pm
  • ArchDemon, tried to do through sum, but in that case I can not figure out how to tie the amount to the desired account. it turned out that the amount was simply assigned to all accounts in a row. - Victor Barannik
  • Nikolaj Sarry, added - Victor Barannik

3 answers 3

 SELECT factures.letter_id, factures.id_number, tarif_ttc, ...остальные.необходимые.поля... , SUM(payment.check_sum) as summary, tarif_ttc - coalesce(SUM(payment.check_sum),0) as needToPay FROM factures LEFT JOIN payment ON payment.facture_date=factures.letter_id AND payment.facture_number=factures.id_number WHERE '$date'<='$date_now' GROUP BY factures.letter_id, factures.id_number

Do not choose from tables * , always specify the columns that you need. Firstly, it speeds up the transfer of data from MySQL to your application, secondly, you will be exactly what columns there are and do not try to contact some $result->summary which is not in the sample (you do not, I have).

If the ONLY_FULL_GROUP_BY option is enabled in your MySQL, you will need to list in the group by all columns to which group functions are not applied and that are present in the select list, and you cannot specify * in group by and you will need to list all the columns of the factures table.

If ONLY_FULL_GROUP_BY turned off, then in GROUP BY it is possible to specify only columns that uniquely ensure the uniqueness of the accounts records (I included the columns on the assumption that this is the date and number of the account, although their names are strange). For the remaining columns not included in group by and without group functions, MySQL will take the first available value, in our case this is sufficient, since group by ensures the uniqueness of accounts.

And you have some wild condition in where . If $date is less than $date_now , then the query will return all rows in the table. If he turns out to be more, then not one. I think you need in comparison to use any columns with dates from the table of accounts. And the $date_now variable is definitely not needed, because in MySQL there is a now() function that returns the current date-time (and current() returns a date without the time)

  • and you can explain how the tarif_ttc field that is present in select will be processed but is absent in the group by - Bald
  • @Bald I wrote for MySQL with ONLY_FULL_GROUP_BY turned off , so they write in 90% of cases. the first value will be taken and it is the only one because in the group by two fields it seems that the components are a unique key. - Mike
  • It is interesting who is the minus and for what, despite the fact that the second answer, where group by uniqueness does not provide, and after sampling it is not clear to which invoice what amount is not touched ... - Mike
  • minus is not mine. re-read the answer did not find in it will be taken the first available value can it is worth including explicitly in the answer? - Bald
  • Thanks decision. it all worked! Also, special thanks for the comments. As a beginner of work with sql queries, much has become clear to me - Victor Barannik

Try this query

 SELECT SUM(payment.check_sum) FROM factures LEFT JOIN payment ON payment.facture_date=factures.letter_id AND payment.facture_number=factures.id_number GROUP BY letter_id

    With the help of a subquery you can find the amount. Those. we pass through the accounts, for each account we summarize the amount of payments

     SELECT *, (SELECT SUM(summary) FROM payment WHERE payment.facture_date=factures.letter_id AND payment.facture_number=factures.id_number) AS payment_summary FROM factures;