SQL Count Payment Plans per customer

[troyz]

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?

 

[PHV]

What about this ?
SELECT Customer, COUNT(DISTINCT PayPlannum), SUM(Payment_Amount)
FROM rmdetailedweekly
GROUP BY Customer

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[troyz]

That works. Thanks for your help.