PostgreSQL: sub-queries and aggregate functions(Sum)

Question

I tried referring to other questions; I've inferred that sub-queries cannot be used on aggregate functions, but I cannot solve this use case.

Tables:  
1. CustomerInfo(c_id,name)  
2. ProductInfo(p_id,price)  
3. ModelInfo(p_id,m_id,name)  
4. PurchaseRecords(c_id,m_id,quantity)

Required output:
List of customer names, with total amount purchased by each customer.

My flow of thought is that:
Link PurchaseRecords with ModelInfo to get p_id,
ModelInfo with ProductInfo to get the price,
Multiply price returned by quantity in PurchaseRecords for every specific customer,
which requires me to link CustomerInfo in the end to get the name.

I'm using Postgres. I could write a program for that in Java, but I find it hard to do it with SQL. So, what is the correct query here? Any pointers on how to think problems out are appreciated!

To make things easier to answer you really should build a schema, with sample data and an expected output based on that. Perhaps using sqlfiddle or a similar tool. — Michel Milezzi
– Michel Milezzi, Commented Dec 28, 2018 at 10:51
I did it on pgAdmin 4 with sample data; how do I share it here? Thanks! — Catalan Cabbage
– Catalan Cabbage, Commented Dec 28, 2018 at 11:09
You could use sqlfiddle. Don't forget to edit your question adding its link. :) — Michel Milezzi
– Michel Milezzi, Commented Dec 28, 2018 at 11:11

Petro K · Accepted Answer · 2018-12-28 11:46:07Z

1

SELECT
  c.name as customer_name,
  sum(coalesce(p.price, 0) * coalesce(pr.quantity, 0)) as amount_purchased
from
  CustomerInfo c
  left join PurchaseRecords pr on c.c_id = pr.c_id
  left join ModelInfo mi on mi.m_id = pr.m_id
  left join ProductInfo p on p.p_id = mi.p_id
group by
  c.name

answered Dec 28, 2018 at 11:46

Petro K

3321 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: sub-queries and aggregate functions(Sum)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related