hello again,

i have a somewhat complicated report that i'm having some trouble figuring out just what i need to do.

everyone loves the car analogy, so let's say i have a data source that looks like this:

MANUFACTURER.|.MODEL.|.TYPE.|.WEIGHT
-------------+-------+------+-------
Chrysler.....|.300...|.4DR..|.3500..
Chrysler.....|.300C..|.4DR..|.3800..
Chrysler.....|.XFIRE.|.2DR..|.2400..
Cadillac.....|.ETC...|.2DR..|.3500..
Cadillac.....|.STS...|.4DR..|.3500..
Lincoln......|.MARKV.|.2DR..|.4500..

and i need to build a report that looks like this:

MANUFACTURER.|..#.OF.MODELS..|..TOTAL.WEIGHT..|..TYPES..|..HEAVIEST.2D..|..TOTAL.4D.WEIGHT.
-------------+---------------+----------------+---------+---------------+------------------
Cadillac.....|.............2.|...........7000.|.4DR,2DR.|..........3500.|.............3500.
Chrysler.....|.............3.|...........9700.|.4DR,2DR.|..........2400.|.............7300.
Lincoln......|.............1.|...........9700.|.....2DR.|..........4500.|..............N/A.  

the first three columns i've got working, i simply did the following:

TABLE FILE My_Cars
SUM
     CNT.MODEL
     WEIGHT
     BY MANUFACTURER

the hard part is, how can i get the "types" column to list all the distinct values in that column that correspond to that manufacturer? in addition, how can i find the "heaviest 2D vehicle", and the total weight for 4D cars?

a) is there any way to do this, and b) is there any way to do this with only one query -- not making and reading a bunch of hold files? the data source i'm accessing is huge and has a lot of security and overhead on it and it is very slow to get even a single result back. the fewer trips i have to make to the data source the better.

thanks very much!

This message has been edited. Last edited by: GreenspanDan,

I am sure - hopeful really - that someone else will show you a better way, but in case no one does:

  
DEFINE FILE CAR
  DOORS/A3 = IF MODEL CONTAINS '4 DOOR' THEN '4DR' ELSE '2DR';
END
TABLE FILE CAR
SUM
   CNT.WEIGHT NOPRINT
BY CAR
PRINT
   COMPUTE C_NOM/I5     = IF CAR EQ LAST CAR THEN LAST C_NOM + 1 ELSE 1; AS '# OF MODELS'
   COMPUTE C_WEIGHT/I11 = IF (CAR EQ LAST CAR) THEN LAST C_WEIGHT + WEIGHT ELSE WEIGHT; AS 'TOTAL WEIGHT'
   COMPUTE C_TYPE/A50   = IF (CAR EQ LAST CAR) AND (LAST C_TYPE NOT CONTAINS DOORS) THEN SUBSTR(50, LAST C_TYPE || ',' || DOORS, 1, 50, 50, 'A50') ELSE 
                          IF (CAR EQ LAST CAR) THEN LAST C_TYPE ELSE DOORS; AS 'TYPES'
   COMPUTE C_2DR /I11   = IF (CAR EQ LAST CAR) AND (DOORS EQ '2DR') AND (WEIGHT GT LAST C_2DR) THEN WEIGHT ELSE 
	                  IF (CAR EQ LAST CAR) THEN LAST C_2DR ELSE 
	                  IF DOORS EQ '2DR' THEN WEIGHT ELSE 0; AS 'HEAVIEST 2D'
   COMPUTE C_4DR /I11   = IF (CAR EQ LAST CAR) AND (DOORS EQ '4DR') AND (WEIGHT GT LAST C_4DR) THEN WEIGHT ELSE 
	                  IF (CAR EQ LAST CAR) THEN LAST C_4DR ELSE 
			  IF DOORS EQ '4DR' THEN WEIGHT ELSE 0; AS 'HEAVIEST 4D'
BY CAR
BY DOORS NOPRINT
WHERE TOTAL CNT.WEIGHT EQ C_NOM;
END

Results:

  CAR            # OF MODELS  TOTAL WEIGHT  TYPES      HEAVIEST 2D  HEAVIEST 4D
  ---            -----------  ------------  -----      -----------  -----------
  ALFA ROMEO               3          7215  2DR,4DR           2320         2590  
  AUDI                     1          2571  2DR               2571            0  
  BMW                      6         11300  2DR,4DR           2400         3250  
  DATSUN                   1          2050  2DR               2050            0  
  JAGUAR                   2          7635  2DR               4200            0  
  JENSEN                   1          4000  2DR               4000            0  
  MASERATI                 1          3700  2DR               3700            0  
  PEUGEOT                  1          2860  4DR                  0         2860  
  TOYOTA                   1          2219  4DR                  0         2219  
  TRIUMPH                  1          2241  2DR               2241            0   

This message has been edited. Last edited by: dhagen,

this is working great, thanks!