Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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'
share|improve this answer

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
share|improve this answer
    
The "transaction" table provides the transdate column. –  Sam Jan 6 '13 at 6:14
    
Thanks; I haven't used CTEs before. Unfortunately, it looks like SQLite doesn't support them. –  Sam Jan 6 '13 at 6:26
    
The ; goes at the end of a statement, even in SQL Server: sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/… and sqlserverplanet.com/tsql/… –  a_horse_with_no_name Jan 6 '13 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 '13 at 0:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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