CODEWITHSUNDEEP
postgresqlcountaggregate
Tim Sullivan
Tim Sullivan

Reputation: 16888

Postgres: Getting a total related count based on a condition from a related table

My sql-fu is not strong, and I'm sure I'm missing something simple in trying to get this working. I have a fairly standard group of tables: 

users
-----
id
name


carts
-----
id
user_id
purchased_at


line_items
----------
id
cart_id
product_id


products
--------
id
permalink

I want to get a total count of purchased carts for each user, if that user has purchased a particular product. That is: if at least one of their purchased carts has a product with a particular permalink, I'd like a count of the total number of purchased carts, regardless of their contents.

The definition a purchased cart is when carts.purchased_at is not null.

select
  u.id,
  count(c2.*) as purchased_carts

from users u

inner join carts c on u.id = c.user_id
inner join line_items li on c.id = li.cart_id
inner join products p on p.id = li.product_id 
left join carts c2 on u.id = c2.user_id

where 
  c.purchased_at is not NULL 
  and
  c2.purchased_at is not NULL 
  and
  p.permalink = 'product-name'
group by 1
order by 2 desc

The numbers that are coming up for purchased_carts are strangely high, possibly related to the total number of line items multiplied by the number of carts? Maybe? I'm pretty stumped at the result. Any help would be greatly appreciated.

Upvotes: 1

Views: 51

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

bool_or is what you need

select
    u.id,
    count(distinct c.id) as purchased_carts
from
    users u
    inner join
    carts c on u.id = c.user_id
    inner join
    line_items li on c.id = li.cart_id
    inner join
    products p on p.id = li.product_id 
where c.purchased_at is not NULL 
group by u.id
having bool_or (p.permalink = 'product-name')
order by 2 desc

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52386

This ought to help:

select u.id,
       count(*)
from   users u join
       carts c on c.user_id = u.id
where  c.purchased_at is not NULL and
       exists (
         select null
         from   carts      c2
         join   line_items l on l.cart_id = c2.id
         join   products   p on p.id      = l.product_id
         where  c2.user_id      = u.id and
                c2.purchased_at is not NULL 
                p.permalink     = 'product-name')
group by u.id
order by count(*) desc;

The exists predicate is a semi-join.

Upvotes: 1

Related Questions