on 07-14-2016 10:00 AM
Hi,
I am developing a SQL calc. view. I reach to a level where I have my data 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
out of above dataset, I need to get below results:
customer country month cost
c001 DK 201506 -100
c001 FR 201507 -200
that is 'within each month', taking 'lowest 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.
WHAT I've tried so far:
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"
the above SQL gives me result like:
c001 US 201506 -100
c001 DK 201506 -100
c001 FR 201507 -200
but i need either like:
c001 DK 201506 -100
c001 FR 201507 -200
OR like:
c001 US 201506 -100
c001 FR 201507 -200
I much appreciate your help how to write SQL to solve this issue.
best regards
Ahmad
Hello Ahmad,
one option would be:
select "customer", "country", "month", "cost" from (
select "customer", "country", "month", min("cost") over (partition by "customer", "month" order by "cost") as "cost", row_number() over (partition by "customer", "month" order by "cost") as "row_num" from "mytable"
)
where "row_num" = 1;
Regards,
Florian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Query given by Florian Pfeffer works.
The small change to the query is:
select customer, country, month, cost from (
select customer, country, month, cost,
row_number() over (partition by customer, month order by cost) as row_num from mytable
)
The difference is, not using aggregate function for cost.
Hi Ahmed,
One more option
If you want result set contain DK:
Select Customer, Min(Country), Month, Cost
FROM "INF713775"."TABLE_JULY14"
Where (Month,Cost) IN (Select Month, Min(Cost) From "INF713775"."TABLE_JULY14" Group By Month)
Group By Customer, Month, Cost
If you want result set contain US:
Select Customer, Max(Country), Month, Cost
FROM "INF713775"."TABLE_JULY14"
Where (Month,Cost) IN (Select Month, Min(Cost) From "INF713775"."TABLE_JULY14" Group By Month)
Group By Customer, Month, Cost
Regards,
Muthuram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.