How to get the count of a row

Mark Tabor

I have a one table in which i am storing customer and their installment amount , there are total 4 installment for each customer ,few customer missed few installment like customer ABC has installment 1 , but then he missed installment # 02 , then he get installment # 03 etc

Installment #	Installment Amount	Customer Name
1	100	ABC
2	100	ABC
3	100	ABC
4	100	ABC
1	100	XYS
3	100	XYS
4	100	XYS
1	100	GGG

As from above table you can see there are three customer , customer ABC get all installment ,customer XYS missed installment # 02 and Customer GGG get only one installment .
Now I want to get the count of all customer who get

1-At least three installment

2-in this query I want to get all those customers who get three consecutive installment like 1,2,3

How to write these two queries in oracle , my table name is customer_installment

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
To get the count, nest the query given and apply group by on customer_name
Nitin Sontakke4y ago
That is very simple, all you have to do is add following WHERE clause to query already given by me.
where ci1.installment_amount is not null and ci2.installment_amount is not null and ci3.installment_amount is not null
Hope it helps!
Mark Tabor4y ago
Thanks Nitin for the reply my issue is not the join as i am having all information in this one customer_installment table , my issue is to find all Those customers who have received three consecutive installments.
Nitin Sontakke4y ago
Hello,

I am NOT a Oracle PL/SQL deverloper, but tried to help nonetheless.

Try the following:
select ci.customer_name, ci1.installment_amount installment_first, ci2.installment_amount installment_second, ci3.installment_amount installment_third, ci4.installment_amount installment_fourth, nvl(ci1.installment_amount, 0) + nvl(ci2.installment_amount, 0) + nvl(ci3.installment_amount, 0) + nvl(ci4.installment_amount, 0) installment_total
from customer_installment ci
left outer join customer_installment ci1 on ci.customer_name = ci1.customer_name and ci1.installment = 1
left outer join customer_installment ci2 on ci.customer_name = ci2.customer_name and ci2.installment = 2
left outer join customer_installment ci3 on ci.customer_name = ci3.customer_name and ci3.installment = 3
left outer join customer_installment ci4 on ci.customer_name = ci4.customer_name and ci4.installment = 4
Even then this approach is strictly technically incorrect.
To be precise, you should be quering from customer table and then left outer join to above result-set.

This is important, because otherwise you will miss the customers who have paid no installment at all.

Hope you get the point.

Hope it helps!