AH_AH

July 13, 2016

thanks for responding.

I am writing like:

SELECT "customer" as "custM", "month" as "monthM", "country" as "countryM", "cost" as "costM"

FROM "mytable" as "M"

inner join

(

SELECT "customer" as "custS", "month" as "monthS", MIN("costSUM") AS "costMIN"

FROM

(SELECT distinct "customer", "country", "month", sum("cost") as "costSUM" FROM "mytable"

group by "customer", "country", "month")

group by "customer","month"

) as "S"

on "M"."customer" = "S"."custS" AND "M"."month" = "S"."monthS" AND "M"."cost"= "S"."costMIN"

this gives me result:

c001 US 201506 -100
c001 DK 201506 -100
c001 FR 201507 -200

but i need like:

c001 DK 201506 -100
c001 FR 201507 -200

OR

c001 US 201506 -100
c001 FR 201507 -200

how can i further enhance this SQL or should I write it in totally different way?

thanks

July 3, 2016

Hi,

I need expert help to solve below problem.

My data in table 'mytable' look like:

customer country month cost
c001 US 201506 -100
c001 DK 201506 -100
c001 DE 201506 -50
c001 FR 201507 -200
c001 UK 201507 -50

I need to get below results:

customer country month cost

c001 DK 201506 -100

c001 FR 201507 -200

that is 'within each month', taking 'hightest cost' generating 'country' for each customer.

CATCH: in this example, we can see that in 201506, cost -100 is same for US and DK. In this case only taking either one, not both.

Thanks for your help.

Regards

Nomann

Posts posted by AH_AH