Sum two SQLite columns, when they're subqueries-CodePudding

I have a table of receipts. Each one is associated with a service, and each person is obligated to pay equally for it, except when they are assigned an extra fee that can be activated/deactivaded (0/1). So I used a subquery to get the extra amount they have to pay only if that fee is active; the table 'fees' contains the user_id, the service_id, the extra amount and the active flag. And then, I should get the total per person, adding the extra fee (if any) to the subtotal (receipt total amount minus any active extra fee, and then divided by the number of persons who are obligated to contribute).

SELECT
    P.nombre AS person,
    S.nombre AS service,
    (
        SELECT TOTAL(C.value)
        FROM fees C
        WHERE C.user_id = P.id AND C.service_id = O.service_id AND C.active = 0
    ) AS fee,
    IFNULL(NULL, 23333) AS subtotal,
    (fee   subtotal) as total
FROM receipts R
    LEFT JOIN obligations O ON O.service_id = R.service_id
    LEFT JOIN persons P ON O.user_id = P.id
    LEFT JOIN services S ON O.service_id = S.id
WHERE R.id = 3 AND O.active = 0;

Note: 23333 (the subtotal) will be replaced with a '?' and then I'll pass as argument to execute the query with Golang that result that I've already got from another function

Problem occurs at this line

(fee   subtotal) as total

Output: no such column: fee

If I run the query without that line, it will actually return a table with the active extra fees and subtotal, but I'm stuck when trying to create a final column to add those two values.

Thanks!

Edit

Following Stefan's advice, here are the statements I used to create the tables:

CREATE TABLE IF NOT EXISTS persons (id INTEGER PRIMARY KEY, name TEXT NOT NULL, active INTEGER DEFAULT 0); CREATE UNIQUE INDEX per_nom_uindex on persons (name)

CREATE TABLE IF NOT EXISTS services (id INTEGER PRIMARY KEY, name TEXT NOT NULL, active INTEGER  DEFAULT 0); CREATE UNIQUE INDEX ser_nom_uindex on services (name)

CREATE TABLE IF NOT EXISTS receipts (id INTEGER PRIMARY KEY, y INTEGER NOT NULL, m INTEGER NOT NULL, service_id INTEGER NOT NULL, amount INTEGER NOT NULL, FOREIGN KEY (service_id) REFERENCES services (id))

CREATE TABLE IF NOT EXISTS fees (id INTEGER PRIMARY KEY, person_id INTEGER NOT NULL, service_id INTEGER NOT NULL, amount INTEGER NOT NULL, active INTEGER DEFAULT 0, FOREIGN KEY(person_id) REFERENCES persons(id), FOREIGN KEY(service_id) REFERENCES services(id))

CREATE TABLE IF NOT EXISTS obligations (id INTEGER PRIMARY KEY, person_id INTEGER NOT NULL, service_id INTEGER NOT NULL, active INTEGER DEFAULT 0, FOREIGN KEY(person_id) REFERENCES persons(id), FOREIGN KEY(service_id) REFERENCES services(id))

CodePudding user response：

Consider moving the subquery from SELECT to JOIN clause (often called derived table) and adjust it with GROUP BY aggregation on user_id and service_id. Doing so, this allows you to reference the column as needed and even avoid rowwise aggregation (unless the SQLite engine runs it as a single aggregation under the hood).

SELECT
    P.nombre AS person,
    S.nombre AS service,
    C.fee,                                  -- REFERENCE SUBQUERY COLUMN
    IFNULL(?, 23333) AS subtotal,
    C.fee   IFNULL(?, 23333) as total       -- REPEAT NEEDED EXPRESSION
FROM receipts R
    LEFT JOIN obligations O 
        ON O.service_id = R.service_id 
    LEFT JOIN persons P  
        ON O.user_id = P.id 
        AND O.active = 0                    -- MOVED FROM WHERE CLAUSE
    LEFT JOIN services S 
        ON O.service_id = S.id
    LEFT JOIN (
        SELECT user_id,
               service_id, 
               TOTAL(value) AS fee
        FROM fees
        WHERE active = 0
        GROUP BY user_id,
                 service_id
    ) C ON C.user_id = P.id
        AND C.service_id = O.service_id
    WHERE R.id = 3