>Suppose that I have a table called politics and with this folowing fields
>
>

>delegates: c(20)
>City:         c(20)
>State:       c(5)
>

>
>And I want to generate this sort or report. Can it be done with an SQL statement?
>(I know how to do it with a do while loop.)
>
>

>delegate, City
>Robert     Phoenix
>Smith     Snow Flake
>Burns     Snow Flake
>etc.
>           Az: Delegates: 3
>
>Stevens  San Diego
>Jerome   Los Angeles
>          Ca:  Delegates 2
>etc.
>etc.
>

>
>Thanks
>Steve


One way is to add a report variable if you group by state

Without report variable, add a field nDelegates. You can even print the #delegates at the top of the State group

	create cursor Politics ;
	(	Delegates	c(20), ;
		City		c(20), ;
		State		c(5) ;
	)
	
	insert into Politics values('Robert', 'Phoenix', 'AZ')
	insert into Politics values('Smith', 'Snow Flake', 'AZ')
	insert into Politics values('Burns', 'Snow Flake', 'AZ')
	
	insert into Politics values('Stevens', 'San Diego', 'CA')
	insert into Politics values('Jerome', 'Los Angeles', 'CA')
	
	
	select State, ;
		   count(*) as nDelegates ;
		from Politics ;
		into cursor tmp ;
		group by 1
	
	select	Politics.Delegates, ;
			Politics.City, ;
			Politics.State, ;
			tmp.nDelegates ;
		from Politics ;
			join tmp on (Politics.State == tmp.State ) ;
		into cursor result ;
		order by 3,2,1