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

Postgresql – join multiple subqueries wtih aggregated data

My data are:

order_id    chef_name   order_returned
1001    Charles McBakey Y
1001    Sarah McCookin  N
1001    John McFry  N
1001    Charles McBakey N
1001    John McFry  N
1001    John McFry  Y
1001    John McFry  Y
1001    Sarah McCookin  N
1001    Charles McBakey N
1001    Sarah McCookin  N
1001    Charles McBakey Y
1001    Charles McBakey N
1001    Sarah McCookin  N
1001    John McFry  N
1001    Sarah McCookin  N
1001    Charles McBakey Y
1001    John McFry  N
1001    Sarah McCookin  Y
1001    John McFry  Y 


select chef_name, count(chef_name) as cnt_total
from order_returns t
group by chef_name

when I used code above, I got the table below:

"chef_name" "cnt_total"
"Sarah McCookin"    6
"Charles McBakey"   6
"John McFry"    7

Then I used code below

select chef_name, count(chef_name) as cnt_y
from order_returns 
where order_returned = 'Y'
group by chef_name

got this table:

"chef_name" "cnt_y"
"Charles McBakey"   3
"John McFry"    3
"Sarah McCookin"    1

then I used code below:

select chef_name,count(chef_name) as cnt_n
from orders
where order_returned = 'N'
group by chef_name

I got this table

"chef_name" "cnt_n"
"Charles McBakey"   3
"John McFry"    4
"Sarah McCookin"    5

then I combined these three tables using following codes:

select chef_name, count(chef_name) as cnt_total
from order_returns t
group by chef_name
join
(select chef_name, count(chef_name) as cnt_y
from order_returns 
where order_returned = 'Y'
group by chef_name) y
on t.chef_name = y.chef_name
join
(select chef_name,count(chef_name) as cnt_n
from order_returns
where order_returned = 'N'
group by chef_name) n
on y.chef_name = n.chef_name

I got the error message:
ERROR: syntax error at or near "join"
LINE 4: join.

Could you anyone help me this out?

Thanks,

Tags:

2

Answers


  1. 0

    You’ve nearly got it.

    You just need to properly isolate the first sub-query as a sub-query in the same way you’ve done the second and third:

    select * from 
(select chef_name, count(chef_name) as cnt_total
from order_returns 
group by chef_name) t
join
(select chef_name, count(chef_name) as cnt_y
from order_returns 
where order_returned = 'Y'
group by chef_name) y
on t.chef_name = y.chef_name
join
(select chef_name,count(chef_name) as cnt_n
from order_returns
where order_returned = 'N'
group by chef_name) n
on y.chef_name = n.chef_name

    I’ve just used select * – once you’ve got the syntax error sorted you’ll probably want to specify specific fields

    select t.chef_name, t.cnt_total, y.cnt_y, n.cnt_n from …

    Login or Signup to reply.
  2. 0

    Your description implies that you are attempting to get the counts for total orders, returned orders, and non-returned orders by chef_name. You do not need to union 3 queries; it can be done in a single query with a single select. You accomplish this with the aggregate function filter clause, in this case count(*): (See demo here)

    select chef_name
     , count(*)                                      cnt_total
     , count(*) filter (where order_returned = 'Y')  cny_y
     , count(*) filter (where order_returned = 'N')  cnt_n 
  from order_returns
 group by chef_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search