PostgreSQL: How to group similar data into one column

Question

I am searching for a query which can group similar data into one column. Basically, I have a table which is representing an order item. Each order item belongs to an order. An order item has an amount, price per unit (ppu), currency (cur) of the ppu and some other costs (each cost with their own currency). Imaging, I have the following data in the table:

id | id_order | amount | ppu | ppu_cur  | costsA | costsA_cur  | costsB | costsB_cur 
1  | Ord1     | 20     | 10  | EUR      | 5      | USD         | 15     | EUR
2  | Ord1     |  8     | 20  | EUR      | 0      |             | 15     | EUR
3  | Ord1     | 15     | 50  | USD      | 10     | USD         | 0      | 
4  | Ord2     | 50     | 25  | EUR      | 10,50  | EUR         | 15     | EUR

Now, what I need is a query which can group the prices like this:

id_order | curr | price_total
Ord1     | EUR  | 390
Ord1     | USD  | 505
Ord2     | EUR  | 1275,5

INFO: "0" prices or prices without a currency should be ignored.

Unfortunately, I managed to do this with the ppu only:

SELECT "sub"."id", "sub"."cur", SUM("sub"."price_total")
FROM (
  SELECT "ord"."id" AS "id", ("oi"."amount" * "oi"."ppu") AS "price_total", "oi"."cur" AS "cur"
  FROM "orders" "ord", "orderitems" "oi"
  WHERE
    ("oi"."id_order" = "ord"."id") AND
    (("oi"."cur" IS NOT NULL) AND ("oi"."cur" <> '')) AND
    ("oi"."price" <> 0)
  ) "sub"
GROUP BY "sub"."id", "sub"."cur"

Has someone an idea how to solve this issue?

Thanks in advance :)

EDIT: Each order price should be calculated based on the currencies, for example: There are two different currencies (EUR, USD) in "Ord1". Therefore, the result must have two rows for "Ord1". On the other hand, there is only one currency (EUR) in "Ord2". So the result contains only one row for "Ord2".

Calculation: Each costsX/ppu with the same currency has to be added. In addition, the ppu has to be multiplied with the amount. Example for "Ord1":

EUR: (20 * 10 + 15) + (8 * 20 + 15)
USD: (5) + (15 * 50 + 10)

INFO: There are only 2 different currencies in the example. However, there could be any number of currencies.

What exactly is the problem with your query? What do you want it to do that it isn't doing? Since you gave an example of what you want to get, can you show what you're getting? — Scotch
– Scotch, Commented Feb 27, 2013 at 16:53
State your requirements a bit more clearly. It sounds like want to multiply Price per Unit (PPU) and the Number of Units (Amount). You want to do this for each Currency code. Then, you want to sum these for an Order. Finally, you want to have each curency calculation on a separate rcord. is that correct? — Darius X.
– Darius X., Commented Feb 27, 2013 at 17:08
The problem with the query is that they do not take the costs (A and B) into account. — QStormDS
– QStormDS, Commented Feb 27, 2013 at 18:27
@user2116206 You keep clarifying the question, but I didn't get any comment on my answer below. If it's not correct, please let me know what I have misunderstood. — Joachim Isaksson
– Joachim Isaksson, Commented Feb 27, 2013 at 19:49
Hi Joachim, sorry for the late reply. Yesterday evening, it was not possible for me to verify your solution (only to answer some questions). Nevertheless, thanks a lot for your answer! It seems to be exactly the query I am looking for. In addition, thtanks for posting the link to SQLfiddle. Great job :) — QStormDS
– QStormDS, Commented Feb 28, 2013 at 7:25

Joachim Isaksson · Accepted Answer · 2013-02-27 17:08:51Z

1

Having separate currencies per column makes it a little trickier, so the easiest way is to make a cte to itemize the prices and select the sums with appropriate grouping from that;

WITH cte AS (
  SELECT id_order,ppu_cur curr,amount*ppu price FROM orderitems  -- amount*ppu
  UNION ALL
  SELECT id_order,"costsA_cur","costsA" FROM orderitems          -- costsA
  UNION ALL
  SELECT id_order,"costsB_cur","costsB" FROM orderitems          -- costsB
)
SELECT id_order,curr,SUM(price)
FROM cte
WHERE curr IS NOT NULL AND price<>0
GROUP BY id_order,curr

An SQLfiddle for testing.

answered Feb 27, 2013 at 17:08

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-02-27 16:57:51Z

0

You don't do a great job of explaining the logic, but I think I got it from the results.

Try this:

select id_order, curr, sum(amount*(ppu+AddedCost)) as price_total
from (select t.*,
             (case when ppu_cur = costsa_cur then costsa
                   when ppu_cur = costSB_cur then costsb
                   else 0
              end) as AddedCost
      from t
     ) t
group by id_order, curr

I'm using a subquery to make the added cost calculation clearer.

answered Feb 27, 2013 at 16:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

QStormDS Over a year ago

Sorry, I just updated the explanation. See section "EDIT". As far as I can see your example only works if the ppu currency is either equals to costa_curr or costb_curr. However, it could be that each curr column has its own currency.

Collectives™ on Stack Overflow

PostgreSQL: How to group similar data into one column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related