CODEWITHSUNDEEP
sqlpostgresql
Ernst
Ernst

Reputation: 514

SQL Count data with conditions on multiple joins

So here is my simplified structure: Company <- User <- Order <- Order status

I need to perform multiple counts of orders for each company. Each count can have its own filters by order_status. In return should be company's id and multiple counts.

Here is what I tried for one count, but I cannot combine it with others counts.

SELECT c.id as company_id, COUNT(o.id) as orders_count_total
    FROM companies c
    LEFT JOIN users u
        ON u.belongs_to_company_id = c.id
    LEFT JOIN orders o
        ON
            c.id = o.users_company_id OR
           (
                u.id = o.users_id AND
                u.belongs_to_company_id = c.id AND
                o.users_company_id is NULL
            )
    JOIN orders_orderstatus o_status
        ON o.status_id = o_status.id AND NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

Also in this case I miss all companies that don't have any orders

Upvotes: 1

Views: 70

Answers (2)

Hambone
Hambone

Reputation: 16407

I'm completely unsure of myself on this, so I have my finger on the "Delete" key prepared to nuke this answer if it's completely wrong, but can you do two left joins, one with each condition, and then coalesce?

SELECT
  c.id as company_id, COUNT(coalesce (o1.id, o2.id)) as orders_count_total
FROM
  companies c
  LEFT JOIN users u
      ON u.belongs_to_company_id = c.id
  LEFT JOIN orders o1
      ON c.id = o1.users_company_id
  left join orders o2 on
      u.id = o2.users_id AND
      u.belongs_to_company_id = c.id AND
      o2.users_company_id is NULL
  JOIN orders_orderstatus o_status
      ON coalesce (o1.status_id, o2.status_id) = o_status.id AND 
      NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

Count should only count non-null results, so I would think this would work.

If not, how about the same coalesce in a CTE and then an inner join to the order status table?

with orders as (
  SELECT
    c.id as company_id, coalesce (o1.id, o2.id) as order_id
  FROM
    companies c
    LEFT JOIN users u
        ON u.belongs_to_company_id = c.id
    LEFT JOIN orders o1
        ON c.id = o1.users_company_id
    left join orders o2 on
        u.id = o2.users_id AND
        u.belongs_to_company_id = c.id AND
        o2.users_company_id is NULL
  where
    o1.id is not null or o2.id is not null
)
select
  o.company_id, count (o.order_id) as orders_count_total
from
  orders o
  JOIN orders_orderstatus o_status on
      o.order_id = o_status.id AND 
      NOT (o_status.slug IN ('slug1', 'slug2'))
GROUP BY company_id

I've had really bad experience with using "OR" conditions in the join, unless the original condition is highly selective -- yours doesn't appear to be, as you seem to want two completely distinct joins.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One problem is that the last JOIN is an inner join. Once you use LEFT JOIN, you should continue with LEFT JOIN:

SELECT c.id as company_id, COUNT(o.id) as orders_count_total
FROM companies c LEFT JOIN
     users u
     ON u.belongs_to_company_id = c.id
     orders o LEFT JOIN
     ON c.id = o.users_company_id OR
        (u.id = o.users_id AND
         u.belongs_to_company_id = c.id AND
         o.users_company_id is NULL
        ) LEFT JOIN
     orders_orderstatus o_status
     ON o.status_id = o_status.id AND 
        o_status.slug NOT IN ('slug1', 'slug2')
GROUP BY company_id;

Upvotes: 0

Related Questions