Page 1 of 1
Total $ by account, pls help
Posted: Fri Sep 27, 2002 11:02 am
by lawful_toad
I'm trying to write this program and am having some problems. The purpose is to report total dollars collected per account #. It searches for payments made for the month adds them up and displays them by account. Unfortunately it's not doing it and I don't know the problem.
I know the code is horribly written but bear with me.
Here is the
code.
I'm lawfultoad on aim.[/url]
Posted: Fri Sep 27, 2002 11:48 am
by riley
Let the database add it for you.
select ssn, Sum(total_amount), loan_no from payment_info where deposit_date like '$searchdate%' and deposit_type = 'Credit' GROUP BY ssn, loan_no ORDER BY ssn
select ssn, total_amount, loan_no from payment_info where deposit_date like '$searchdate%' and deposit_type = 'Credit' ORDER BY ssn
Posted: Fri Sep 27, 2002 12:06 pm
by lawful_toad
But I am looking for individual totals i.e.
customer riley made 3 payments of $10 each for the month I'm reporting
so i report riley, cust # and the total amount of your payments($30).
An average report contains about 200 cust totals.
Posted: Fri Sep 27, 2002 12:30 pm
by riley
By using the Group By ssn, loan_no in the query you will get one row of ssn, loan_no, and the sum of each unique ssn, loan_no. So even if one person (ssn) has multiple loan_no(s) you will get the sum for each combination. Also in the query statement add (as Total) behind Sum(total) ie Sum(Total) as Total.
I.E.
###-##-1234 loan_no_xxx $10
###-##-1234 loan_no_xxx $10
###-##-1234 loan_no_xxx $10
###-##-1234 loan_no_yyy $10
Your resulting recordset will look like
ssn loan_no Total
###-##-1234 loan_no_xxx $30
###-##-1234 loan_no_yyy $10
Posted: Fri Sep 27, 2002 1:16 pm
by lawful_toad
Okay, I ran your query through the query analyzer (i'm using mssql w/ php) and for the totals, I got null values except for where the customer made only one payment(there was no addition needed).
This was along the lines of my output with the php program. The total_amount field is data_type money, why would I be getting null values?
Posted: Fri Sep 27, 2002 1:52 pm
by lawful_toad
ack, I had null values in the fields and I was trying to do math on them

That's why I was getting NULL values returned.
Posted: Fri Sep 27, 2002 2:12 pm
by lawful_toad
Now my second query pulls member information just for display puprposes, I'm having a problem now because the query is displaying customer names twice because there isn't any clause to tell it otherwise.
Any suggestions?
Posted: Fri Sep 27, 2002 2:46 pm
by riley
SELECT DISTINCT [Column] From [Table]
You will get a row of data for each unique column(s combination) entry. Therefore if you Select Distinct Lastname, Firstname will be different from Select Distinct Lastname. Limiting the number of columns will limit the distinct rows of data you get (ps don't ask for more columns than you really need)