Re: how to do this GROUP BY in SQL*PLUS

In article <fragante.707006826_at_chilko.ucs.ubc.ca> fragante_at_unixg.ubc.ca (Gv Fragante) writes:
>
>What I would like to do is group by each SALESMAN with their corresponding
>series of TICKET_NO. The result would be like this:
>
>
>SALESMAN TICKET_NO_FROM TICKET_NO_TO COUNT
>-------- --------------- ------------ -----
>JOHN 1 50 50
> 76 100 25
>
>RICK 51 75 25
> 176 200 25
>
>TOM 151 175 25
>
>WAYNE 76 150 75
>
>
>My initial query looked like this:
>
>SELECT SALESMAN, MIN(TICKET_NO), MAX(TICKET_NO), COUNT(TICKET_NO)
> FROM TICKETS
> GROUP
> BY SALESMAN
>
>
>However, this does not work because it fails to get two rows for JOHN and RICK.
>This is because it cannot sense that JOHN and RICK have *TWO* series of
>tickets.

I've dealt with similar problems by:

1. spooling SELECT output to a file, with all print cosmetics turned off.
2. using the query: SELECT SALESMAN, TICKET_NO FROM TICKETS ORDER BY SALESMAN, TICKET_NO
3. running a Unix shell script to convert the SELECT output into the form you want.

Hope this helps.

Ken Salter Received on Fri May 29 1992 - 18:05:12 CEST