1

I have written the following SQLite query:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT "transaction".transamount AS amount
    FROM checkingaccount_v1 AS "transaction"
    JOIN Category_v1 AS category ON "transaction".CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON "transaction".SubCategID = subcategory.SubCategID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'

    UNION ALL

    SELECT splittransaction.splittransamount AS amount
    FROM splittransactions_v1 AS splittransaction
    JOIN Category_v1 AS category ON splittransaction.CategID = category.CategID
    JOIN SubCategory_V1 AS subcategory ON splittransaction.SubCategID = subcategory.SubCategID
    JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID
    WHERE "transaction".transdate BETWEEN date('2012-08-11') AND date('now')
        AND category.categname = 'Food'
        AND subcategory.subcategname = 'Groceries'
) AS costs

I would like to know how I can simplify it. The two queries that are UNION ALLed are quite similar. Some values, such as the literal date, are also duplicated.

SQLite doesn't appear to support functions, stored procedures or variables. For this particular query, I require SQLite and can't use another programming language (like Perl) to execute it, so please don't suggest other tools/technologies.

If anyone is curious, the query is for a MoneyManagerEx report.

2 Answers 2

1

I've managed to simplify it a bit:

SELECT sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM
(
    SELECT transdate, transamount AS amount, CategID, SubCategID
    FROM checkingaccount_v1 AS "transaction"

    UNION ALL

    SELECT transdate, splittransamount AS amount, splittransaction.CategID, splittransaction.SubCategID
    FROM splittransactions_v1 AS splittransaction
    JOIN checkingaccount_v1 AS "transaction" USING (TransID)
)
JOIN Category_v1 AS category USING (CategID)
JOIN SubCategory_V1 AS subcategory USING (SubCategID)
WHERE transdate BETWEEN date('2012-08-11') AND date('now')
    AND categname = 'Food'
    AND subcategname = 'Groceries'
0

Can you explain the purpose of this join in the 2nd half of your query?

JOIN checkingaccount_v1 AS "transaction" ON splittransaction.TransID = "transaction".TransID

I can't experiment against your data, but would a common table expression for the two different data sources work?

;WITH AccountTransactions AS
(
    SELECT  transamount AS amount, TransID, 0 As Split, 
            transdate, categname, subcategname
    FROM    checkingaccount_v1
    UNION ALL
    SELECT  splittransamount AS amount, TransID, 1 AS Split, 
            transdate, categname, subcategname
    FROM    splittransactions_v1
), TransactionData AS
(
    SELECT  amount, TransID, Split
    FROM    AccountTransactions
    JOIN    Category_v1 AS category ON AccountTransactions.CategID = category.CategID
    JOIN    SubCategory_V1 AS subcategory ON AccountTransactions.SubCategID = subcategory.SubCategID
    WHERE   transdate BETWEEN date('2012-08-11') AND date('now')
        AND categname = 'Food'
        AND subcategname = 'Groceries'
)
SELECT  sum(amount) / (julianday('now', '+1 day') - julianday('2012-08-11')) * 14 AS 'Cost Per Fortnight'
FROM    TransactionData
4
  • The "transaction" table provides the transdate column.
    – Sam
    Jan 6, 2013 at 6:14
  • Thanks; I haven't used CTEs before. Unfortunately, it looks like SQLite doesn't support them.
    – Sam
    Jan 6, 2013 at 6:26
  • 1
    The ; goes at the end of a statement, even in SQL Server: sqlblog.org/2009/09/03/… and sqlserverplanet.com/tsql/…
    – user1822
    Jan 6, 2013 at 11:51
  • Sorry, bad habit. I was in SQL Server land, and most queries I have seen are written without the optional (in MS SQL) terminating semicolon.
    – Colin
    Jan 10, 2013 at 0:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.