CREATE TABLE TestTable (ID INT, CONTRACT_ID VARCHAR(100), STAT_NEW VARCHAR(100), UPD_DT DATETIME);
INSERT INTO TestTable VALUES (0 ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (1 ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (2 ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (3 ,'CR 01 to MWO 1','Sent to VMO','12-AUG-13');
INSERT INTO TestTable VALUES (4 ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (5 ,'CR 01 to MWO 1','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (6 ,'CR 01 to MWO 1','Sent to VMO','12-AUG-13');
INSERT INTO TestTable VALUES (7 ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (8 ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (9 ,'CR 01 to MWO 2','Sent to xxxx Project Team','11-AUG-13');
INSERT INTO TestTable VALUES (10 ,'CR 01 to MWO 2','Sent to VMO','12-AUG-13');
INSERT INTO TestTable VALUES (11 ,'CR 01 to MWO 3','Sent to xxxx Project Team','12-AUG-13');
INSERT INTO TestTable VALUES (12 ,'CR 01 to MWO 3','Sent to xxxx Project Team','12-AUG-13');
INSERT INTO TestTable VALUES (13 ,'CR 01 to MWO 3','Sent to VMO','13-AUG-13');
SELECT CONTRACT_ID,COUNT(STAT_NEW) STAT_NEW_COUNT FROM
(
SELECT t.*
,(SELECT COUNT(id) FROM TestTable WHERE STAT_NEW='Sent to VMO' AND ID < t.ID) AS cnt
from TestTable t
WHERE STAT_NEW<>'Sent to VMO'
) tt
GROUP BY CONTRACT_ID,cnt;
Q1JFQVRFIFRBQkxFIFRlc3RUYWJsZSAoSUQgSU5ULCBDT05UUkFDVF9JRCBWQVJDSEFSKDEwMCksIFNUQVRfTkVXICBWQVJDSEFSKDEwMCksIFVQRF9EVCAgREFURVRJTUUpOwoKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoMCAgICwnQ1IgMDEgdG8gTVdPIDEnLCdTZW50IHRvIHh4eHggUHJvamVjdCBUZWFtJywnMTEtQVVHLTEzJyk7CklOU0VSVCBJTlRPIFRlc3RUYWJsZSBWQUxVRVMgKDEgICAsJ0NSIDAxIHRvIE1XTyAxJywnU2VudCB0byB4eHh4IFByb2plY3QgVGVhbScsJzExLUFVRy0xMycpOwpJTlNFUlQgSU5UTyBUZXN0VGFibGUgVkFMVUVTICgyICAgLCdDUiAwMSB0byBNV08gMScsJ1NlbnQgdG8geHh4eCBQcm9qZWN0IFRlYW0nLCcxMS1BVUctMTMnKTsKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoMyAgICwnQ1IgMDEgdG8gTVdPIDEnLCdTZW50IHRvIFZNTycsJzEyLUFVRy0xMycpOwpJTlNFUlQgSU5UTyBUZXN0VGFibGUgVkFMVUVTICg0ICAgLCdDUiAwMSB0byBNV08gMScsJ1NlbnQgdG8geHh4eCBQcm9qZWN0IFRlYW0nLCcxMS1BVUctMTMnKTsKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoNSAgICwnQ1IgMDEgdG8gTVdPIDEnLCdTZW50IHRvIHh4eHggUHJvamVjdCBUZWFtJywnMTEtQVVHLTEzJyk7CklOU0VSVCBJTlRPIFRlc3RUYWJsZSBWQUxVRVMgKDYgICAsJ0NSIDAxIHRvIE1XTyAxJywnU2VudCB0byBWTU8nLCcxMi1BVUctMTMnKTsKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoNyAgICwnQ1IgMDEgdG8gTVdPIDInLCdTZW50IHRvIHh4eHggUHJvamVjdCBUZWFtJywnMTEtQVVHLTEzJyk7CklOU0VSVCBJTlRPIFRlc3RUYWJsZSBWQUxVRVMgKDggICAsJ0NSIDAxIHRvIE1XTyAyJywnU2VudCB0byB4eHh4IFByb2plY3QgVGVhbScsJzExLUFVRy0xMycpOwpJTlNFUlQgSU5UTyBUZXN0VGFibGUgVkFMVUVTICg5ICAgLCdDUiAwMSB0byBNV08gMicsJ1NlbnQgdG8geHh4eCBQcm9qZWN0IFRlYW0nLCcxMS1BVUctMTMnKTsKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoMTAgICwnQ1IgMDEgdG8gTVdPIDInLCdTZW50IHRvIFZNTycsJzEyLUFVRy0xMycpOwpJTlNFUlQgSU5UTyBUZXN0VGFibGUgVkFMVUVTICgxMSAgLCdDUiAwMSB0byBNV08gMycsJ1NlbnQgdG8geHh4eCBQcm9qZWN0IFRlYW0nLCcxMi1BVUctMTMnKTsKSU5TRVJUIElOVE8gVGVzdFRhYmxlIFZBTFVFUyAoMTIgICwnQ1IgMDEgdG8gTVdPIDMnLCdTZW50IHRvIHh4eHggUHJvamVjdCBUZWFtJywnMTItQVVHLTEzJyk7CklOU0VSVCBJTlRPIFRlc3RUYWJsZSBWQUxVRVMgKDEzICAsJ0NSIDAxIHRvIE1XTyAzJywnU2VudCB0byBWTU8nLCcxMy1BVUctMTMnKTsKCgpTRUxFQ1QgQ09OVFJBQ1RfSUQsQ09VTlQoU1RBVF9ORVcpIFNUQVRfTkVXX0NPVU5UIEZST00KKAogICAgU0VMRUNUIHQuKgoJCSwoU0VMRUNUIENPVU5UKGlkKSBGUk9NIFRlc3RUYWJsZSBXSEVSRSBTVEFUX05FVz0nU2VudCB0byBWTU8nIEFORCBJRCA8IHQuSUQpIEFTIGNudAoJZnJvbSBUZXN0VGFibGUgdAoJV0hFUkUgU1RBVF9ORVc8PidTZW50IHRvIFZNTycKKSB0dApHUk9VUCBCWSBDT05UUkFDVF9JRCxjbnQ7Cg==