CODEWITHSUNDEEP
sqlpostgresqljoin
Almas Abdrazak
Almas Abdrazak

Reputation: 3612

Join data from three related tables

In Postgres I have the following 4 tables

user (
id,
name

);
order(
id,
user_id
)
service(
id,
name

);
order_to_service(
id,
order_id,
service_id
price
);

I want to write a query to see User name , amount of orders of this user and how much money he spend on all orders Example:

name   amount  price
Albert   100    3200

This is my query

select u.name , count(o.user_id),sum(ots.price)
from orders o inner join users u on u.id=o.user_id
inner join order_to_service ots on ots.order_id = o.id

where(o.user_id is not null) group by u.name

This is my result:

"Аlbert";55;29978

Accodring to this result user with name Albert has 55 orders But using this query 

select count(*) from orders where user_id = (select id from users where name like 'Albert')
Result is 33

What's wrong with my first query?

Upvotes: 0

Views: 58

Answers (1)

kc2018
kc2018

Reputation: 1460

If the relationship between Orders table and Order_to_service table is one to many, then you would need to sum up the price per order in Order_to_service table before joining with the Orders table. Try this:

select u.name , count(o.user_id),sum(ots.price_tot)  
from orders o inner join users u on u.id=o.user_id  
inner join ( select order_id, sum(price) as price_tot  
             from order_to_service    
             group by order_id ) ots   
          on ots.order_id = o.id  
where (o.user_id is not null) group by u.name

Upvotes: 1

Related Questions