Varying Calculations for Rows in SQLite-CodePudding

My SQL table is set out as such:

Key (primary key)
Parent key (many primary keys to a parent key)
Points 1
Points 2
Modifier

The modifier determines the effect on total points. I.e.

Modifier A - total points = (points 1 points 2)*0.1
Modifier B - total points = (points 1 points 2)*0.2
Modifier C - total points = (points 1 points 2)*0.7

Ideally I'd like to produce a table of:

Parent key
Total modified points for that key (e.g. 100 * 0.2 150 * 0.5 180 * 0.7)

I cannot get it to work for the life of me. The closest I've come is in the code below, which outputs a row for each parent key and a column for each modifier. However, each column is filled with a single value despite these statements all producing the values I want in isolation.

SELECT table.parent, t1.one, t1.two, t1.three
FROM
table
LEFT JOIN

(SELECT 
(SELECT ((sum(points1) sum(points2))*0.1) FROM table WHERE (modifier = "modifier one") GROUP BY key) as 'one', 
(SELECT ((sum(points1) sum(points2))*0.2) FROM table WHERE (modifier = "modifier two") GROUP BY key) as 'two',
(SELECT ((sum(points1) sum(points2))*0.7) FROM table WHERE (modifier = "modifier three") GROUP BY key) as 'three'
) t1

GROUP BY table.parent

CodePudding user response：

You can do it with conditional aggregation:

SELECT parent,
       SUM((points1   points2) * CASE WHEN modifier = 'modifier one' THEN 0.1 ELSE 0 END) one,
       SUM((points1   points2) * CASE WHEN modifier = 'modifier two' THEN 0.2 ELSE 0 END) two,
       SUM((points1   points2) * CASE WHEN modifier = 'modifier three' THEN 0.7 ELSE 0 END) three
FROM tablename
GROUP BY parent;

Or, with TOTAL() aggregate function:

SELECT parent,
       0.1 * TOTAL((points1   points2) * (modifier = 'modifier one')) one,
       0.2 * TOTAL((points1   points2) * (modifier = 'modifier two')) two,
       0.7 * TOTAL((points1   points2) * (modifier = 'modifier three')) three
FROM tablename
GROUP BY parent;

Or, if your version of SQLite is 3.30.0 , with the FILTER clause:

SELECT parent,
       0.1 * TOTAL(points1   points2) FILTER (WHERE modifier = 'modifier one') one,
       0.2 * TOTAL(points1   points2) FILTER (WHERE modifier = 'modifier two') two,
       0.7 * TOTAL(points1   points2) FILTER (WHERE modifier = 'modifier three') three
FROM tablename
GROUP BY parent;