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]
Total $ by account, pls help
Moderator: General Moderators
-
lawful_toad
- Forum Newbie
- Posts: 12
- Joined: Fri Sep 27, 2002 11:02 am
- Location: collinsville, il
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
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
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
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
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?
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
-
lawful_toad
- Forum Newbie
- Posts: 12
- Joined: Fri Sep 27, 2002 11:02 am
- Location: collinsville, il
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)
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)