>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

distinct can only be used once in the SQL statement and may be your problem. So, I did a prequery on just one of the DISTINCT columns, then ran a join with distinct for second...

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 ;
	date1,;
	COUNT( distinct( sno )) as snocnt;
    FROM ;
	testsub ;
    GROUP BY;
	1 ;
    INTO ;
	CURSOR C_DistinctSNO READWRITE 
INDEX on date1 TAG date1
		
SELECT ;
	testsub.date1,;
	C_DistinctSNO.snocnt,;
	COUNT( distinct( testsub.icode )) as icodecnt,;
	SUM( testsub.weight ) totweight;
    FROM ;
	testsub, ;
	C_DistinctSNO;
    WHERE ;
	testsub.date1 = C_DistinctSno.date1;
    GROUP BY;
	1, 2 ;
    INTO ;
	CURSOR C_Subtotals