Site icon Mortgage Blog Pro

postgresql query to get sum and columns from two tables using joins

These are the tables that I have created: Tables

  1. I want to get accounts.account,accounts.type,DATE(transactions.date),transactions.transactionid,transactions.amount,transactions.note from two tables between ’10-11-2021′ and ’31-12-2021′.(whatever type may be)
  2. I want to get Sum(account) from transactions table where type=”income” and between ’10-11-2021′ and ’31-12-2021′.
  3. I want to get Sum(account) from transactions table where type=”expense” and between ’10-11-2021′ and ’31-12-2021′.

But I need all three queries in a single statement(that’s what I am struggling)

My query:

SELECT  accounts.account,accounts.type,DATE(transactions.date),transactions.transactionid,transactions.amount,transactions.note
FROM transactions
FULL JOIN accounts ON transactions.accountid=accounts.accountid
WHERE transactions.date BETWEEN '{0}' AND '{1}' ORDER BY transactions.date
UNION
  select sum(amount)
  FROM transactions
  FULL JOIN accounts ON transactions.accountid=accounts.accountid 
  WHERE accounts.type='income'

I need to add other two queries also to fit above

can anyone help me?

Exit mobile version