Calculate total price with 'WITH RECURSIVE'

schw4ndi

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.

user4637357

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Calculate total price with 'WITH RECURSIVE'

From Dev

How to calculate total price in php

From Dev

Model rails calculate total price

From Dev

Calculate total price of item list

From Dev

JavaScript Calculate total price of items

From Dev

Javascript calculate tax and total price

From Dev

How to calculate total price of order?

From Dev

Using Forms in HTML to calculate total price

From Dev

How to calculate total price of an order with Rails?

From Dev

Laravel Eloquent: Best Way to Calculate Total Price

From Dev

Calculate total with price and quantity using Angularjs

From Dev

Calculate Total Price from multiple toggle Buttons

From Dev

Can't calculate total price > 30

From Dev

Calculate order total price in node js and mongodb

From Dev

How to calculate total price of an order with Rails?

From Dev

calculate total price from checkbox + select list

From Dev

How can I calculate total price of an invoice?

From Dev

Calculate subtotal and total price depending on selected options using jquery

From Dev

How to get the number of Items in multiple Spinner View and calculate the total price

From Dev

Calculate the max total price of rods, cut with unique length value

From Dev

Jquery calculate sub total from fields - qty and price by class and grand Total sum

From Dev

How can i calculate total positive ,total negative price and sum using Node.js and Mongoose

From Dev

The formula for "Two-for" price is 20% off the total price of two items. Prompt the user for each of the values and calculate the result

From Dev

MYSQL and PHP calculate total price of orders table every day and every month, populate new summary table

From Dev

How can I calculate total price with piece in GridView in ASP.NET

From Dev

PHP: calculate total price according to which options a user chooses from HTML form

From Dev

How to calculate total sales in MongoDB but the product price data comes from different collection

From Java

Calculating the total price in React

From Dev

Update total price?

Related Related

  1. 1

    Calculate total price with 'WITH RECURSIVE'

  2. 2

    How to calculate total price in php

  3. 3

    Model rails calculate total price

  4. 4

    Calculate total price of item list

  5. 5

    JavaScript Calculate total price of items

  6. 6

    Javascript calculate tax and total price

  7. 7

    How to calculate total price of order?

  8. 8

    Using Forms in HTML to calculate total price

  9. 9

    How to calculate total price of an order with Rails?

  10. 10

    Laravel Eloquent: Best Way to Calculate Total Price

  11. 11

    Calculate total with price and quantity using Angularjs

  12. 12

    Calculate Total Price from multiple toggle Buttons

  13. 13

    Can't calculate total price > 30

  14. 14

    Calculate order total price in node js and mongodb

  15. 15

    How to calculate total price of an order with Rails?

  16. 16

    calculate total price from checkbox + select list

  17. 17

    How can I calculate total price of an invoice?

  18. 18

    Calculate subtotal and total price depending on selected options using jquery

  19. 19

    How to get the number of Items in multiple Spinner View and calculate the total price

  20. 20

    Calculate the max total price of rods, cut with unique length value

  21. 21

    Jquery calculate sub total from fields - qty and price by class and grand Total sum

  22. 22

    How can i calculate total positive ,total negative price and sum using Node.js and Mongoose

  23. 23

    The formula for "Two-for" price is 20% off the total price of two items. Prompt the user for each of the values and calculate the result

  24. 24

    MYSQL and PHP calculate total price of orders table every day and every month, populate new summary table

  25. 25

    How can I calculate total price with piece in GridView in ASP.NET

  26. 26

    PHP: calculate total price according to which options a user chooses from HTML form

  27. 27

    How to calculate total sales in MongoDB but the product price data comes from different collection

  28. 28

    Calculating the total price in React

  29. 29

    Update total price?

HotTag

Archive