AH_AH Posted July 3, 2016 Share Posted July 3, 2016 Hi, I need expert help to solve below problem. My data in table 'mytable' look like: customer country month costc001 US 201506 -100c001 DK 201506 -100c001 DE 201506 -50c001 FR 201507 -200c001 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted July 5, 2016 Share Posted July 5, 2016 That's the lowest cost, -100 is less than -50, not greater than. You can group by the month, country, and customer and select the minimum value for cost. That will return multiple records though, so you'll need to loop through them to figure out which one you want. Link to comment Share on other sites More sharing options...
rawatjai Posted July 9, 2016 Share Posted July 9, 2016 You can use (GROUP BY country) to get all rows for same records of (cost). And as @justsomeguy said your result is showing lowest values not the highest one. Link to comment Share on other sites More sharing options...
AH_AH Posted July 13, 2016 Author Share Posted 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 -100c001 DK 201506 -100c001 FR 201507 -200 but i need like: c001 DK 201506 -100c001 FR 201507 -200 OR c001 US 201506 -100c001 FR 201507 -200 how can i further enhance this SQL or should I write it in totally different way? thanks Link to comment Share on other sites More sharing options...
justsomeguy Posted July 13, 2016 Share Posted July 13, 2016 I believe you would need to use a subquery or a join to filter things like that, it's easier to filter records like that in another language where you get the records from the database. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now