CODEWITHSUNDEEP
sqlpostgresqlgroup-bysubquery
Mudassir Ali
Mudassir Ali

Reputation: 8041

Do not include select columns in group by

Here are my tables(including only relevant columns)

Table: carts
address_id - integer

Table: addresses
name - varchar
phone - varchar

Table: orders
order_number - integer (this is the foreign key for cart table)

I want to fetch phone number of the customers who have ordered only once, so I constructed the following query

select addresses.phone 
from orders 
   inner join carts on orders.order_number = carts.id 
   inner join address on carts.address_id = addresses.id 
group by addresses.phone 
having count(orders.*) = 1;

This works great! But I do also need to select customer name & order number and I updated the select statement to

select addresses.phone, addresses.name, orders.order_number ...

Now, postgres urges me to include these columns in GROUP BY clause but this will not return me the desired result.

I tried using subquery as following which seems to get me the desired result

select addresses.phone, (select ad.name from addresses ad where ad.phone = addresses.phone) ...

But using subquery is the only way to go about this ? or is there any simpler/optimal way ?

Upvotes: 2

Views: 132

Answers (1)

user330315
user330315

Reputation:

You can achieve this with a window function which doesn't require everything to be grouped:

select *
from (
  select addresses.phone, addresses.name, orders.order_number, 
         count(orders.order_number) over (partition by addresses.phone) as cnt
  from orders 
     inner join carts on orders.order_number = carts.id 
     inner join address on carts.address_id = addresses.id 
) t 
where cnt = 1;

Upvotes: 2

Related Questions