Skip to content

Instantly share code, notes, and snippets.

@telagraphic
Last active August 13, 2016 14:52
Show Gist options
  • Save telagraphic/2e3af3c39211a761af37fe9ba4aceddf to your computer and use it in GitHub Desktop.
Save telagraphic/2e3af3c39211a761af37fe9ba4aceddf to your computer and use it in GitHub Desktop.
Postgresql Union
Tables to Union Join
Expenses
Bills
Income
On
Categories
I have 28 categories that I would like to sum all transaction amounts for by a given time period.
When I run each select statement for each table by itself, I get the 28 rows per each category.
When I use the UNION for all three tables, I get more than 28 records.
It includes the 'duplicate' category when there is a amount for that category.
I know that UNION will not include duplicates...
I need to rewrite this query to return expenses, bills and income summed against each category.
I would prefer to do this in the database than using some reduce on the client.
I have tried several re-writes, but any suggestions on how to write a query would be helpful.
Still not seeing the solution...
SELECT cat.name AS category, SUM(ex.amount) AS weekly_total, trx.id AS type
FROM categories cat
FULL OUTER JOIN expenses ex ON (cat.id = ex.categorytype)
AND date_trunc('month', ex.trxdate) = date_trunc('month', current_date)
INNER JOIN transaction_type trx ON (cat.transactiontype = trx.id)
GROUP BY cat.name, trx.id
UNION
SELECT cat.name AS category, SUM(bl.amount) AS weekly_total, trx.id AS type
FROM categories cat
FULL OUTER JOIN bills bl ON (cat.id = bl.categorytype)
AND date_trunc('month', bl.paiddate) = date_trunc('month', current_date)
INNER JOIN transaction_type trx ON (cat.transactiontype = trx.id)
GROUP BY cat.name, trx.id
UNION
SELECT cat.name AS category, SUM(inc.netamount) AS weekly_total, trx.id AS type
FROM categories cat
FULL OUTER JOIN income inc ON (cat.id = inc.categorytype)
AND date_trunc('month', inc.payday) = date_trunc('month', current_date)
INNER JOIN transaction_type trx ON (cat.transactiontype = trx.id)
GROUP BY cat.name, trx.id;
Returns..
Cateogry Amount TrxType
Alcohol 2
Alcohol 7.7 2
Bill Payment 2
Bill Payment 6.5 2
Car Fuel 2
Car Fuel 35.2 2
Car Insurance Bill 131.96 1
Car Insurance Bill 1
Card Payment 4
Cash 7
Cash Withdrawal 4
Checking 7
Clothes Expense 4
Credit 7
Dine Out 4
Dine Out 92.33 4
Electric Bill 2
Gas Bill 2
Gift 4
Groceries 2
Groceries 181.81 2
House Expense 4
House Expense 5.25 4
Internet Bill 62.52 1
Internet Bill 1
Loan 6
Loan Payment 234.9 4
Loan Payment 4
Miscellaneous 4
Paycheck 5
Paycheck 1434.37 5
Personal Expense 4
Phone Bill 1
Rent 950 1
Rent 1
Retirement 7
Saving 7
Tech Expense 30.22 4
Tech Expense 4
Water Bill 2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment