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

Join table in SQL doesn't return that I want – Postgresql

I havetwo table like this
Table:customers
Table:orders
and want to display like
join

I’ve tried

select 
concat(c.first_name, ' ', c.last_name) as customer_name,
COUNT(o.customer_id) as total_orders,
sum(o.amount) as total_amount_order
from customers c
join orders o on c.id = o.customer_id  
where o.amount >= 500
group by o.customer_id, c.first_name, c.last_name, o.amount

but it’s just returned only David Robinson data. What’s wrong with my query? Any advice? Thanks

Tags:

2

Answers


  1. 0

    please try this

    select 
concat(c.first_name, ' ', c.last_name) as customer_name,
COUNT(o.customer_id) as total_orders,
sum(o.amount) as total_amount_order
from customers c
join orders o on c.id = o.customer_id   
group by o.customer_id, c.first_name, c.last_name
having  sum(o.amount) >= 500
    Login or Signup to reply.
  2. 0

    The WHERE clause is executed before the GROUP BY clause, so it’s filtering out every purchase except Mr. Robinson’s very expensive monitor before the aggregate sum.

    I suspect you want to filter for SUM(o.amount) >= 500, but you can’t put that in the WHERE clause because they’re executed before the aggregate is done.

    You need to use a HAVING clause, which is the same as a WHERE but executed after the aggregation, allowing you to do HAVING SUM(o.amount) >= 500.

    I would show you the updated query, but I have a feeling this is homework. 😉

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search