Total $ by account, pls help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
lawful_toad
Forum Newbie
Posts: 12
Joined: Fri Sep 27, 2002 11:02 am
Location: collinsville, il

Total $ by account, pls help

Post 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]
User avatar
riley
Forum Commoner
Posts: 45
Joined: Thu May 02, 2002 6:31 pm

Post 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
lawful_toad
Forum Newbie
Posts: 12
Joined: Fri Sep 27, 2002 11:02 am
Location: collinsville, il

Post 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.
User avatar
riley
Forum Commoner
Posts: 45
Joined: Thu May 02, 2002 6:31 pm

Post 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
lawful_toad
Forum Newbie
Posts: 12
Joined: Fri Sep 27, 2002 11:02 am
Location: collinsville, il

Post 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?
lawful_toad
Forum Newbie
Posts: 12
Joined: Fri Sep 27, 2002 11:02 am
Location: collinsville, il

Post 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.
lawful_toad
Forum Newbie
Posts: 12
Joined: Fri Sep 27, 2002 11:02 am
Location: collinsville, il

Post 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?
User avatar
riley
Forum Commoner
Posts: 45
Joined: Thu May 02, 2002 6:31 pm

Post 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)
Post Reply