SQL & PL/SQL

Hi
I have the following query with analytic function but wrong results on the last column COUNT.
Please help me to achive the required result.Need to change the way how I select the last column.

1)I am getting the output order by b.sequence_no column . This is a must.
2)COUNT Column :
I don't want the total count based on thor column hence there is no point in grouping by that column.
The actual requirement to achieve COUNT is:
2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1
(In other words, if it is different from the following row)

2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different.
(In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .

My present query:

select    r.name REGION ,
          p.name PT,
          do.name DELOFF,
          ro.name ROUTE,
 decode(th.thorfare_name,'OSIUNKNOWN',NULL,th.thorfare_name)
           THOR,
         l.name LOC ,
          b.sequence_no SEQ,
           CASE WHEN th.thorfare_name = LAG (th.thorfare_name)
            OVER (order by b.sequence_no)
            or th.thorfare_name = LEAD (th.thorfare_name)
            OVER (order by b.sequence_no)
            THEN  COUNT(b.sequence_no) OVER (partition by r.name,th.thorfare_name,l.name order BY b.sequence_no
             )
          ELSE 1
          END COUNT
from   t_regions r,t_post_towns p,t_delivery_offices do, t_routes ro, t_counties c,t_head_offices ho,
t_buildings b,t_thoroughfares th,t_localities l
where   th.thorfare_id = b.thorfare_id
and    nvl(b.invalid,'N')='N'
and    b.route_id=ro.route_id(+)
and    b.locality_id =l.locality_id(+)
and    ro.delivery_office_id=do.delivery_office_id(+)
and    do.post_town_id = p.post_town_id(+)
and    p.ho_id=ho.ho_id(+)
and     ho.county_id = c.county_id(+)
and     c.region_id = r.region_id(+)
and    r.name='NAAS'
and    do.DELIVERY_OFFICE_id= &&DELIVERY_OFFICE_id
and    ro.route_id=3405
group by r.name,p.name,do.name,ro.name,th.thorfare_name,l.name,b.sequence_no
ORDER BY ro.name,b.sequence_no;

My incorrect output[PART OF DATA]:
>
REGION PT DELOFF ROUTE THOR LOC SEQ COUNT
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 1 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 2 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 PRIMHILL CEL 4 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 5 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 THEGROVE CEL 2 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 7 3
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 8 4
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 9 5
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 10 6
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 11 7
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 12 8
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 15 2
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 19 3
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 24 4
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 29 5
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 34 6
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 39 7
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 42 2
NAAS NAAS MAYNOOTH MAYNOOTHR010 PRIMHILL CEL 43 2
NAAS NAAS MAYNOOTH MAYNOOTHR010 PRIMHILL CEL 44 3


My required output[PART OF DATA]-Please compare with the above.:
>
REGION PT DELOFF ROUTE THOR LOC COUNT
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 PRIMHILL CEL 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 THEGROVE CEL 1
NAAS NAAS MAYNOOTH MAYNOOTHR010 NEWTOWNRD CEL 6
NAAS NAAS MAYNOOTH MAYNOOTHR010 DUBLINRD CEL 7
NAAS NAAS MAYNOOTH MAYNOOTHR010 PRIMHILL CEL 2

NOTE :Count as 1 is correctly coming.
But where there is same rows and I want to take the total count on them, I am not getting.
Pls pls help.
Thanks

Edited by: Krithi on 04-Nov-2010 05:28