How to create a new sql table from existing table with small calculations

user_7454130 0
Viewed 59

I have a SQLite table like this:

id item totalcost sharedppl paidby second third
1 Lunch 150 3 Tom Jack Harry
2 Dinner 200 2 Jack Harry
3 Drinks 75 3 Harry Jack Tom

I want a new SQLite table to show share of each person. It needs to do the calculation to split the cost for each item between the people.

item Tom Jack Harry
Lunch 50 50 50
Dinner 0 100 100
Drinks 25 25 25

Please advise what query I need to run on sql to get this new table.

case sqlite sql
2 Answers

One approach, using an unpivot followed by a pivot and aggregation:

WITH cte AS (
    SELECT item, totalcost, paidby AS person FROM yourTable
    UNION ALL
    SELECT item, totalcost, second FROM yourTable
    UNION ALL
    SELECT item, totalcost, third FROM yourTable
)

SELECT
    item,
    CASE WHEN COUNT(CASE WHEN person = 'Tom' THEN 1 END) > 0
         THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Tom,
    CASE WHEN COUNT(CASE WHEN person = 'Jack' THEN 1 END) > 0
         THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Jack,
    CASE WHEN COUNT(CASE WHEN person = 'Harry' THEN 1 END) > 0
         THEN MAX(totalcost) / COUNT(person) ELSE 0 END AS Harry
FROM cte
GROUP BY item, id
ORDER BY id;

Side note: Your current table design is not normalized properly. You can and should change it to the output you want here.

user_18632290

Use a CASE expression that calculates the share for each person:

SELECT item,
       CASE WHEN 'Tom' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Tom,
       CASE WHEN 'Jack' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Jack,
       CASE WHEN 'Harry' IN (paidby, second, third) THEN 1.0 * totalcost / sharedppl ELSE 0 END AS Harry
FROM tablename

See the demo.

user_104988280
Related