GROUP BY MULTIPLE

[#1]

Hi,

I am having an issue with my GROUP BY in my script.  I am trying to GROUP BY both MARKET_SERVICE and MARKET_HOSPITAL but I keep getting the error "Invalid Expression" when I get to the Market Assessment table.  I have tried the following:

- Adjusted my GROUP BY to only the one field

Can anyone tell me what I need to change in my script to get the GROUP BY to work.  I have attached a copy of the script I am using to this discussion. 

Thanks

Brian

[alexandros17]

when you group by all Fields different from the one used in group by must be "aggregated",

so you have to sum even

MARKET_QUANTITY

MARKET_PRICE

and aggregate with minstring function the field MARKET_SERVICE_DESC

I don't know if the result is what you expect but the syntax of group by requires aggregation function on other fields.

Hope it helps

[jagan]

Hi,

If you use Group by in your statement then you have to specify all the fields used as columns in Group by otherwise you will get the error.

LOAD MARKET_SERVICE,
MARKET_HOSPITAL,
sum(MARKET_PRICE*MARKET_QUANTITY) as MARKET_REVENUE
RESIDENT Temp_Market_Assessment

GROUP BY MARKET_SERVICE,MARKET_HOSPITAL;

Service:

LOAD DISTINCT     

     MARKET_SERVICE,
MARKET_SERVICE_DESC

RESIDENT  Temp_Market_Assessment;

DROP TABLE Temp_Market_Assessment

I think MARKET_SERVICE, MARKET_SERVICE_DESC are master data.

Hope this helps you.

Regards,

Jagan.