2

I am having some difficulty generating a query that works in the situation I am in. Here are the details...

1 table = billing
1 table = billing dates

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

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

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...

 $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";

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? MORE
When I do the following the correct values show...

 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

but in the original example, it doesnt always pull the correct value?

Improve this question
5
  • can u show sample data & expected result? Commented Oct 1, 2012 at 6:45
  • try using join instead of left join(just to confirm the output) then check, i think null values may be causing problem Commented Oct 1, 2012 at 6:59
  • results are as follows... id_999333/263/john smith/250/300/-50 Commented Oct 1, 2012 at 7:15
  • when using just the join rather than the left join, i do get more results with the null, but they are in line with each other, original values in respective rows are unchanged. Commented Oct 1, 2012 at 7:19
  • results are as follows... id_999333/263/john smith/250/300/-50 when it should be id_999333/263/john smith/250/150/100 Commented Oct 1, 2012 at 7:21

2 Answers 2

Reset to default
1

Okay, I believe I understand what's happening.

So, presumably either table can have more than one record per login_id, which is why you're doing the sum and group by. However, take a look at what your query gives you back when you take out the aggregates and the group by and I think you may understand the problem.

Let's say you have table 1 which has 2 records for login_id xxx1234 and table 2 which has 3 records for that login_id. Then when you left join them, you will get 6 records.

e.g.

table 1:
  login, custotal
  xxx111, 10
  xxx111, 20

table 2:
  login, amount
  xxx111, 30
  xxx111, 40
  xxx111, 50

after the join:
  login, custotal, amount
  xxx111 10, 30
  xxx111 10, 40
  xxx111 10, 50
  xxx111 20, 30
  xxx111 20, 40
  xxx111 20, 50

So you will be potentially getting several times the amounts you expected in your sums.

What you instead want to do is something like this:

 SELECT 
 billing_totals.login_id, billing_totals.primary_key, billing_totals.a_name, 
 billing_totals.finaltotal,
 billing_dates_totals.paidtotal,
 (billing_totals.finaltotal - billing_dates_totals.paidtotal) AS total
 FROM
   (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) billing_totals 
 LEFT JOIN 
   (SELECT $mysql_billing_dates.login_id, 
   SUM($mysql_billing_dates.amount) AS paidtotal 
   FROM $mysql_billing_dates GROUP BY login_id) billing_dates_totals 
 ON (billing_totals.login_id = billing_dates_totals.login_id) 
 ORDER BY a_name ASC";

Probably need to tweak that slightly as I don't have a mysql to test it on at the moment.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

i want it to look in the db and pull each invoice, add up the custotal and hold it for a calculation to subtract from the sum from another table field.
Updated the answer above. Your join is giving you more records than you think, so your sums are wrong.
you are absolutely correct. I did tweak it and it works as needed. While I study how you came up with this, where would I put a where clause such as 'invoicedate <= 20120515' - by the way, you are awesome, I would've never figured this one out. Thanks a million!
It's a three step process. 1. Get the sum from billing table (the indented SELECT on the left side of the join). 2. Get the sum from billing_dates table (the indented SELECT on the right side of the join). 3. Join the results on id and calculate the difference in sums (the unindented outer SELECT). So a new where clause should go in whichever step makes sense.
now I understand, nice. Thank you for the explanation and again, I appreciate your time and knowledge.
0

$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, SUM($mysql_billing.custotal) - SUM($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";

EDIT : I tried some thing like this for u. 

SELECT a.id, (select sum(custotal) from `billing` where id=a.id) as total1,(select sum(amount) 
from `billing_dates`
where id=a.id) as total1 from `billing` a
Improve this answer

9 Comments

that did give different results, but only in the total field, for some reason, I am getting incorrect values in the finaltotal and the paidtotal fields. I have, for example, 3 separate invoices in the billing table for john smith. the 3 amounts are 100,100,100 yet the custotal prints out 5261.13 - but...some rows are correct, cant figure out why
can u show sample data & expected result? (for every table.) if u can show data like this stackoverflow.com/questions/10974525/… would be more easier to get an idea.
maybe I am confused, they are showing as finaltotal, and paidtotal.
I am recognizing that the values in the paidtotal and finaltotal are being multiplied. Almost like I need a distinct sum?
finaltotal value contain the sum of billing.custotal. so i like to see these billing.custotal data without sum.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.