>Dear Experts
>

>CREATE CURSOR testsub (date1 d(8),sno n(2),icode n(2),weight n(2))
>INSERT INTO testsub VALUES ({^2007/01/01},1,5,10)
>INSERT INTO testsub VALUES ({^2007/01/01},3,7,20)
>INSERT INTO testsub VALUES ({^2007/01/01},3,7,30)
>INSERT INTO testsub VALUES ({^2007/01/02},1,3,40)
>INSERT INTO testsub VALUES ({^2007/01/02},2,3,50)
>INSERT INTO testsub VALUES ({^2007/01/02},1,3,60)
>INSERT INTO testsub VALUES ({^2007/01/03},1,8,70)
>

>I want to scan
>COUNT of distinct sno,icode
>and sum of weight group by date1
>
>The query result must be as follows
>01-07-2006----2-----2----60
>02-07-2006----2-----1----150
>03-07-2006----1-----1----70
>
>Please help

CREATE CURSOR testsub (date1 d(8),sno n(2),icode n(2),weight n(2))
INSERT INTO testsub VALUES ({^2007/01/01},1,5,10)
INSERT INTO testsub VALUES ({^2007/01/01},3,7,20)
INSERT INTO testsub VALUES ({^2007/01/01},3,7,30)
INSERT INTO testsub VALUES ({^2007/01/02},1,3,40)
INSERT INTO testsub VALUES ({^2007/01/02},2,3,50)
INSERT INTO testsub VALUES ({^2007/01/02},1,3,60)
INSERT INTO testsub VALUES ({^2007/01/03},1,8,70)

SELECT testsub.Date1,;
       COUNT(DIST SNo) AS SnoCnt,;
       Tst.Icnt        AS ICodeCnt,;
       SUM(Weight)     AS Weight;
FROM testsub;
INNER JOIN (SELECT Date1, COUNT(DIST icode ) AS Icnt;
                   FROM testsub;
                   GROUP BY Date1) Tst;
ON testsub.Date1 = Tst.Date1;
GROUP BY testsub.Date1,Tst.Icnt;
INTO CURSOR crsTest
BROWSE NORMAL