thnaks Sridhar..

i managed to solve it after a few changes to the data
(Find FastEthernet. Replace with Fa
and Find GigabitEthernet. Replace with Gi)

SELECT F2, LEFT(F3,4) AS interface, COUNT(LEFT(F3,4)) AS aCount FROM
Table1
where F8<>"up / up" GROUP BY F2, LEFT(F3,4) ORDER BY F2, aCount