CODEWITHSUNDEEP
sqlpostgresqljoin
HareshKannan
HareshKannan

Reputation: 103

SQL split values on join PostgreSQL

I have a table called production_cost.

product     | per_day_product_cost
------------+-------------------------
powder      | 40

And, I have another table called daily_production.

date        | product    | type
------------+------------+----------
2018-09-09  | powder     | talcum
2018-09-09  | powder     | chilli

I need to fetch results from both the table which joins on product column. Since the entire per day cost is 40, I need to split the values across both the type of products, which results in

date        | product    | type     | cost
------------+------------+----------+--------
2018-09-09  | powder     | talcum   | 20
2018-09-09  | powder     | chilli   | 20

I tried case when using count and I ran out of ideas to achieve the end result. I am new to back end queries, so if there is any other way to do it, let me know that as well.

Upvotes: 0

Views: 808

Answers (5)

user10461200
user10461200

Reputation: 1

select 
    d.date, 
    d.product, 
    d.type, 
    p.per_day_product_cost/(select count(1) 
                            from daily_production d1 
                            where d1.product=d.product and d1.date=d.date) cost 
from daily_production d,production_cost p 
where d.product=p.product;

Upvotes: 0

S-Man
S-Man

Reputation: 23676

Disclaimer: This works only if all single products always have exactly the same costs. If one ingredient might be gold with 100 cost and the other wood with 10 this does not work anymore. I assume that the number of ingredients per product work as divider for the whole product cost.

Using window function COUNT (https://www.postgresql.org/docs/current/static/tutorial-window.html):

demo: db<>fiddle

SELECT 
    dp.*, 
    pc.per_day_product_cost / 
         count(*) OVER (PARTITION by pc.product, dp.prod_date) as cost
FROM
    daily_production dp
    JOIN production_cost pc
    ON dp.product = pc.product

The COUNT window functions groups the rows into groups. In your case a group is created for every date and product. Then it counts all rows in these groups. This result is taken as divider through your per_day_production_cost and is set as new column into every row.

Notice that "date" is a reserved word in Postgres. It would be better to rename your column. I renamed it into "prod_date".

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use join and sub-query

select t1.*,t2.cost from 
(
 select p.* from production_cost c 
 join daily_production p
 on c.product=p.product
) as t1 join 
( 
select p.product, c.per_day_product_cost/count(p.product) as cost
  from production_cost c 
   join 
 daily_production p
on c.product=p.product
group by p.product,c.per_day_product_cost
) as t2 
on t1.product=t2.product


date                    product type   cost
09/09/2018 00:00:00     powder  talcum  20
09/09/2018 00:00:00     powder  chilli  20

Result demo

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

You can try to use COUNT window function to get total count by product from daily_production table. then write a subquery to get each product total cost from production_cost.

CREATE TABLE  production_cost(
   product VARCHAR(50),
   per_day_product_cost INT
);

INSERT INTO production_cost VALUES ('powder' ,40);

CREATE TABLE daily_production(
   date   TIMESTAMP, product VARCHAR(50), type VARCHAR(50)
);



INSERT INTO daily_production VALUES ('2018-09-09', 'powder','talcum');
INSERT INTO daily_production VALUES ('2018-09-09', 'powder','chilli');

Query 1:

SELECT *,(
    select SUM(per_day_product_cost)  
    from production_cost pc
    WHERE pc.product = dp.product
    GROUP BY pc.product) /COUNT(*) OVER(PARTITION BY product ORDER BY product) as cost
FROM daily_production dp

Results:

|                 date | product |   type | cost |
|----------------------|---------|--------|------|
| 2018-09-09T00:00:00Z |  powder | talcum |   20 |
| 2018-09-09T00:00:00Z |  powder | chilli |   20 |

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try below with subquery for counting product type count and use then count to divide total cost of product

select d.date,d.product,d.type,(p.per_day_product_cost/c) as cost
from product p
inner join
(select product,count(type) as c from daily_production
group by product)a on p.product=a.product
inner join daily_production d on p.product=d.product

Upvotes: 0

Related Questions