Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Count Payment Plans per customer

Status
Not open for further replies.

troyz

IS-IT--Management
Dec 5, 2004
79
0
0
US
I have a table that lists a payment plan by customer number, payment plan number, payment amount. I wrote sql to group by customer number and payment plan number to calculate balance and number of payments left. One Customer Number may have 2 payment plans. I need to show the number of payment plans for the customer.

select
customer
,payplannum
,count(customer)
,cast(sum(payment_amount) as numeric(10,2))
from rmdetailedweekly
group by customer,payplannum

Sample Data:

Customer PayPlannum Payment
12345 ABC2134 59
12345 ABC2134 59
12345 ABC2134 59
89231 ABC6135 79
89231 ABC6135 79
89231 ABC6135 79
89231 ABC6135 79
74425 ABC3531 54
12345 ABC9230 29
12345 ABC9230 29
12345 ABC9230 29
12345 ABC9230 29
12345 ABC9230 29

In the sample data customer 12345 has 2 different payplannum

need to return a 2 (and sum amounts) for this customer do I need to create a subquery for this?
 
What about this ?
SELECT Customer, COUNT(DISTINCT PayPlannum), SUM(Payment_Amount)
FROM rmdetailedweekly
GROUP BY Customer

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
That works. Thanks for your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top