Question 114364 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select sum from multiple tables into one row

I’m fetching data from a view that I later process through an ETL and eventually push to a database.

Here’s an insight on the columns of that view:

date      |contractid|netamount|grossamount|ordertype|paymenttype|
----------+----------+---------+-----------+---------+-----------+
2020-08-01|   1179444|     5883|       6824|RECURRING|BILL       |
2020-08-01|   1179444|      216|        250|FEE      |BILL       |
2020-08-01|   1179445|      444|        500|RECURRING|BILL       |
2020-08-01|   1179445|      111|        222|FEE      |BILL       |

My goal is to count those contracts and see the amount of revenue earned per contract. The catch is that a contract can have additional revenues generated by fees, as in the example above.

The issue I am facing is that if I count the contracts + the fees, then the total number of contracts is wrong since it would also count the fees as unique contract.

Additionally, all the costs that belong to a same contract have the same contractId.

My question is:
How do I select a single row per contractId but with the sum of the fees included? That way, I would have the aggregated sum under one contract only.

The result would be something like:

date      |contractid|netamount|grossamount|ordertype|paymenttype|
----------+----------+---------+-----------+---------+-----------+
2020-08-01|   1179444|     6099|       7074|RECURRING|BILL       |
2020-08-01|   1179445|      555|        722|RECURRING|BILL       |

What I first tried was to use common table expressions to first select all contracts of type ‘RECURRING’. Then in another CTE select all contracts of type ‘FEE’, and eventually union it or join it all via contractId from the first CTE with the contractId from the second CTE.

Unfortunately, I wasn’t able to achieve what I was looking for. Any hint/help would be highly appreciated. Thank you

Tags:

2

Answers


  1. 0 
    select ov.date, ov.contactid, ov2.netamount, ov2.grossamount, 
ov.ordertype, ov.paymenttype 
from original_view ov
left outer join (select sum(netamount) netamount, 
sum(grossamount) grossamount, contactId
  from original_view group by contactid) ov2  on ov.contactid = ov2.contactid
where ordertype = 'RECURRING'
    Login or Signup to reply.
  2. 0

    You can just do a count of distinct and them sum totals like

    SELECT COUNT(DISTINCT contractid) as contracts, 
       SUM(netamount) as net,
       SUM(grossamount) as gross
FROM table_name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search