Jump to content

AH_AH

Members
  • Posts

    2
  • Joined

  • Last visited

AH_AH's Achievements

Newbie

Newbie (1/7)

0

Reputation

  1. 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
  2. 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
×
×
  • Create New...