Postgresql SUM with filter group column

Question

I have this relational data model

product_table

ID	type	configurable_product_id
1	CONFIGURABLE	null
2	SIMPLE	1
3	SIMPLE	1
4	SIMPLE	1

product_source

ID	product_id	quantity
1	2	50
2	3	20
3	4	10

A table that contains the list of products, with two types: configurable and simple. The simple products are linked to a configurable product. A product_source table that contains the quantities of the different products. I would like to make a query to retrieve all the quantities of the products, in this way: If configurable product, then the quantity is the sum of the quantities of the simple products If simple product, then it is the quantity of the simple product only.

Here is the expected result with the above data:

result

product_id	quantity
1	80
2	50
3	20
4	10

Do you have any idea how to proceed ?

For the moment, I have thought about this type of request, but I don't know how to complete the 'CONFIGURABLE' case

SELECT 
pr.id, 
pr.type, 
pr.configurable_product_id,
CASE
    WHEN (pr.type = 'SIMPLE') THEN ps.quantity
    WHEN (pr.type = 'CONFIGURABLE') THEN (????)
END AS quantity
FROM public."product" as pr
LEFT JOIN public."product_source" as ps
ON ps.product_id = pr.id

Pooya · Accepted Answer · 2021-11-16 12:35:43Z

1

You can use window function and partition for calculating sum of quantity (it was like group by)

demo

SELECT 
  pr.id AS product_id,
  CASE
    WHEN (pr.type = 'SIMPLE') THEN ps.quantity
    WHEN (pr.type = 'CONFIGURABLE') THEN SUM(ps.quantity) over ()
  END AS quantity
FROM public."product" as pr
LEFT JOIN public."product_source" as ps
ON ps.product_id = pr.id
ORDER BY pr.id

answered Nov 16, 2021 at 12:35

Pooya

3,2032 gold badges16 silver badges21 bronze badges

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

4 Comments

JamesStandbridge Over a year ago

Is this solution faster than the one coming from Hashed Tomato, with join ?

Pooya Over a year ago

It's maybe faster because don't check case condition in every row. You can check with explain and compare both query

hashed tomato Over a year ago

This code did not work when product table contains more than one CONFIGURABLE product isnt it?

Pooya Over a year ago

Yeah, it does maybe not work. It is dependent on data and conditions. When using the windows function you should care about partition and filter. Maybe if append more data can improve query

hashed tomato · Accepted Answer · 2021-11-16 12:57:21Z

0

Your (...) is:

(
  SELECT SUM(ps2.quantity) 
  FROM product as pr2
  LEFT JOIN product_source as ps2
  ON ps2.product_id = pr2.id
  WHERE pr2.configurable_product_id = pr.id
)

answered Nov 16, 2021 at 12:57

hashed tomato

2441 silver badge4 bronze badges

1 Comment

JamesStandbridge Over a year ago

Work fine, thanks !

Collectives™ on Stack Overflow

Postgresql SUM with filter group column

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related