I have a table 'Leistung' (which means activity in english) and one for the match between two activitys with a given amount (benoetigt). So for example the activity with the ID 2 needs three activitys with the ID 3.
Each activity (leistung) has a price.
I now wanna calculate the total price for all activitys (leistung) including the price from the child activitys in the right amount.
Here are my tables with some testdata:
CREATE TABLE Leistung (
lnr INTEGER PRIMARY KEY,
bezeichnung VARCHAR(100) NOT NULL,
price NUMERIC(7,2) NOT NULL
);
CREATE TABLE benoetigt (
lnr INTEGER REFERENCES Leistung(lnr),
benoetigt_lnr INTEGER REFERENCES Leistung(lnr),
amount INTEGER NOT NULL,
PRIMARY KEY (lnr,benoetigt_lnr)
);
INSERT INTO Leistung(lnr, bezeichnung, price) VALUES
(1, 'Discokugel', 100),
(2, 'Strobolicht', 100),
(3, 'Verstärker', 90),
(4, 'Gelaender',50),
(5, 'Bühne',50),
(6, 'Bühnenelement',20),
(7, 'Stromverteiler', 40),
(8, 'Stromkabel', 20);
INSERT INTO benoetigt (lnr, benoetigt_lnr, amount) VALUES
(5,6,12),
(1,7,1),
(2,7,1),
(7,8,1);
I am pretty shure that the solution only works with a 'WITH RECURSIV' query. My first attempt ist this:
WITH RECURSIVE benoetigtlist(lnr,benoetigt_lnr,menge) AS (
SELECT lnr, benoetigt_lnr, menge FROM benoetigt
UNION ALL
SELECT b.lnr, b.benoetigt_lnr, b.menge
FROM benoetigtlist bl, benoetigt b
WHERE b.lnr = bl.benoetigt_lnr
)
SELECT * FROM benoetigtlist;
But i don't know how i can make the link between this result with the pricing from the activitys.
Or is this the wrong attempt?
Here is a SQL Fiddle from the testdata for better understanding: SQL Fiddle
EDIT: The desired output is a list with all activitys with the total price, including the price of their children.
For the testdata above the result should look like:
id text price
1 'Discokugel' 160 // 100 + 1*40 + 1*20
2 'Strobolicht' 160 // 100 + 1*40 + 1*20
3 'Verstärker' 90
4 'Gelaender' 50
5 'Bühne' 290 // 50 + 12*20
6 'Bühnenelement' 20
7 'Stromverteiler' 60 // 40 + 1*20
8 'Stromkabel' 20
For example the activity with the ID 5 'Bühne' has a total price of 290, because the activity intself costs 50. Also this activity needs twelf activitys of ID 6 (which costs 20) (according to the first entry in 'benoetigt').
In summary: 50 + 12*20 = 290
In the as duplicate marked question the recursion only happens in one table. But i have the problem that the price information is in another table.
Now you need to join result of your recursive CTE with your activity table twice (one join to get parent's price, another join to get price of child activity) and group by parent activity, summarizing cost of child activities:
WITH RECURSIVE benoetigtlist(lnr,benoetigt_lnr,menge) AS (
SELECT lnr, benoetigt_lnr, amount
FROM benoetigt
UNION ALL
SELECT bl.lnr, b.benoetigt_lnr, b.amount
FROM benoetigtlist bl
, benoetigt b
WHERE b.lnr = bl.benoetigt_lnr
)
SELECT parent.lnr
, parent.bezeichnung
-- replace null with 0 if there are no child activities
, parent.price + COALESCE(children_cost.cost, 0) AS cost
FROM Leistung parent
-- subquery that summarizes total cost of all children:
LEFT JOIN (
SELECT bl.lnr, SUM(bl.menge * l.price) as cost
FROM benoetigtlist bl
INNER JOIN Leistung l on bl.benoetigt_lnr = l.lnr
GROUP BY bl.lnr
) children_cost ON children_cost.lnr = parent.lnr
ORDER BY parent.lnr
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments