Sqlite complex query inner join and sum

Question

I am completely new with Sqlite, i have an account table with this structure:

CREATE TABLE accounts(id integer primary key, accountName text not null, currency text not null, accountImage text not null);

And a records table with this structure:

create table records(id integer primary key,amount integer, category text not null, description text, date datetime, recordImage text not null, accountId integer, foreign key(accountId) references accounts(id));

Iam triying to perform this query to return a sum of the amount on records where category is equal to income as total_income and the same for category equal to expense as total_expense, here is the query:

SELECT *, (
    SELECT SUM(records.amount)  AS total_income FROM records
    WHERE records.category = "income", records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
), (
    SELECT SUM(records.amount) AS total_expense FROM records
    WHERE records.category = "expense", 
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
    ) 
    FROM accounts INNER JOIN records ON accounts.id = records.accountId;

However i am getting this error:

Error: near ",": syntax error

Please help me.

Answer 1

You have a comma instead of AND or OR in the 2 WHERE clauses.

Instead of (see comments -- <<<<<<<<<< ...):-

SELECT *, (
    SELECT SUM(records.amount)  AS total_income FROM records
    WHERE records.category = "income", -- <<<<<<<<<< comma not AND or OR
       records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
), (
    SELECT SUM(records.amount) AS total_expense FROM records
    WHERE records.category = "expense", -- <<<<<<<<<< comma not AND or OR
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
    ) 
    FROM accounts INNER JOIN records ON accounts.id = records.accountId;

Although not a syntax error, you probably want the AS clause outside of the subqueries

Try :-

SELECT *, (
    SELECT SUM(records.amount)  FROM records -- <<<<<<<<<<< remove the AS clause
    WHERE records.category = "income" AND -- <<<<<<<<<< AND instead of comma
       records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
) AS total_expense, -- <<<<<<<<<< Moved to name the column in the result
(
    SELECT SUM(records.amount) FROM records -- <<<<<<<<<<< remove the AS clause
    WHERE records.category = "expense" AND -- <<<<<<<<<< AND instead of comma
records.date BETWEEN DATE("now", "start of month")  AND DATE("now", "start of month", "+1 month", "-1 day")
) AS total_expense -- <<<<<<<<<< Moved to name the column in the result
FROM accounts INNER JOIN records ON accounts.id = records.accountId;

e.g.

• Note that each row will have the sum so it's not a running accumulation rather every result will have the same summed values.

Additional re comment :-

as you say I am getting "repeated rows" for every record associated with an account and not an accumulation. I will pass this info to a recyclerView Adapter so it would be a problem. Now I end the query with FROM accounts and I get just one row per account with their corresponding total.

I believe the following may be what you want or perhaps the basis of what you want:-

-- Create testing schema and data
DROP TABLE IF EXISTS accounts;
DROP TABLE If EXISTS records;
CREATE TABLE IF NOT EXISTS records (amount INTEGER, category TEXT, date TEXT, accountId INTEGER);
CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, accountname TEXT);
INSERT INTO accounts (accountname) VALUES('account1'),('account2');
INSERT INTO records (amount, category, date, accountId) VALUES 

    -- account 1
    (300,'income','2018-12-31',1),
    (25,'expense','2018-12-31',1),
    (100,'income','2019-01-01',1),
    (30,'expense','2019-01-01',1),
    (40,'expense','2019-01-02',1),
    (200,'income','2019,01-02',1),

    -- account 2
    (600,'income','2018-12-31',2),
    (325,'expense','2018-12-31',2),
    (700,'income','2019-01-01',2),
    (330,'expense','2019-01-01',2),
    (440,'expense','2019-01-02',2),
    (5200,'income','2019,01-02',2)
;

/* The query
        assumes that the records table is not a WITHOUT ROWID table
        and that the id reflects the insertion order which reflects
        the order in which transactions are considered to have happened
*/
SELECT *, 
    r.rowid AS ambiguos_recordsid, -- for demo/testing
    date('now','start of month') AS startdate, -- for demo/testing
    date('now','start of month', '+1 month', '-1 day') AS enddate, -- for demo/testing
    r.date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day') AS resultdate, -- for demo/testing
    (
        SELECT sum(amount) 
        FROM records
        WHERE 
            records.rowid <= r.rowid AND category = 'income'
            AND (date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day'))
            AND accountid = r.accountid
    ) AS rolling_income,
    (
        SELECT sum(amount)
        FROM records
        WHERE 
            records.rowid <= r.rowid AND category = 'expense'
            AND (date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day'))
            AND accountid = r.accountid
    ) AS rolling_expense
FROM records AS r JOIN accounts on accountId = accounts.id
WHERE r.date BETWEEN date('now','start of month') AND date('now','start of month', '+1 month', '-1 day')
ORDER BY r.accountid, r.rowid
;

Using the above, the result is :-