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

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'

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

;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