StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POmysql query on 2 tables with sum
text
Body
copied!<p>I am having some difficulty generating a query that works in the situation I am in. Here are the details...</p> <p>1 table = billing<br> 1 table = billing dates</p> <p>billing = basically when an invoice is generated, it creates a row in billing with a primary key, an invoice id, the users id, the users username, their actual name, the invoice date, the invoice total, and the payments made</p> <p>billing dates = when a payment is made to an invoice, it creates a row in billing dates with a primary key, an id (which is the same as the primary key in billing table), an invoice id (same as the invoice id in billing), the users id (same as users id in billing), the date paid, the amount paid</p> <p>I am trying to create an ageing report that will pull each outstanding invoice and display it in a 30/60/90/90+ table in PHP to the end user. So if invoice #12 has a $100 balance on it, along with two $10 payments and the invoice is 60 days old, it would show this info in the report. Here is what I have...</p> <pre><code> $sql17 = "SELECT $mysql_billing.login_id, $mysql_billing.primary_key, $mysql_billing.a_name, SUM($mysql_billing.custotal) AS finaltotal, SUM($mysql_billing_dates.amount) AS paidtotal, $mysql_billing.custotal - $mysql_billing_dates.amount AS total FROM $mysql_billing LEFT JOIN $mysql_billing_dates ON $mysql_billing.login_id = $mysql_billing_dates.login_id GROUP BY login_id ORDER BY a_name ASC"; </code></pre> <p>when I run that, some are correct, while most are not. I cannot figure out what the inconsistency is. Any ideas would be greatly appreciated and maybe I am going down the wrong road? <strong>MORE</strong><br> When I do the following the correct values show...</p> <pre><code> SELECT $mysql_billing.login_id, $mysql_billing.primary_key, $mysql_billing.a_name, SUM($mysql_billing.custotal) AS finaltotal FROM $mysql_billing GROUP BY login_id ORDER BY a_name ASC </code></pre> <p>but in the original example, it doesnt always pull the correct value?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload