Joining multiple tables in PostgreSQL, and counting joined table rows

Question

I have three tables. I'm trying to query my PRICE_LIST table, and count the number of SKUs that have a price in each price list, as well as the number of customers assigned to each price list.

Tables

My PRICE_LIST table:

price_list_id
price_number
name

This table has 41 rows.

My SKU_PRICE table:

sku_id
price_number

This table has 1,132 rows.

My CUSTOMER table:

customer_id
price_number
customer_type
is_active

This table has 6,535 rows, but I only want customers of type "E" that are active, so the number of rows I want drops to 2,961.

Query

My query:

SELECT
  pl.price_list_id,
  pl.price_number,
  pl.name,
  count(sp.sku_id)     "sku_count",
  count(c.customer_id) "customer_count"
FROM price_list pl
  LEFT JOIN sku_price sp ON (sp.price_number = pl.price_number)
  LEFT JOIN customer c ON (c.price_number = pl.price_number)
WHERE c.customer_type = 'E' AND c.is_active = 'T'
GROUP BY pl.price_list_id, pl.price_number, pl.name;

Problem

The problem I have is that the results are just crazy:

 price_list_id | price_number | name  | sku_count | customer_count
---------------+--------------+-------+-----------+----------------
            31 |            4 | SF0   |         0 |            792
            33 |            6 | SF2   |     30525 |          30525
             2 |            2 | ASNP2 |       972 |            972
             1 |            1 | ASNP1 |      1596 |           1596
            34 |            7 | SF3   |       616 |            616
            37 |           10 | SF6   |         0 |             51
            32 |            5 | SF1   |      1144 |           1144

The fact that I'm getting a count of 30,525 for both SKU count and customer count tells me that the join is doing weird stuff. I also can't see why I don't get price lists that have no products and no customers (and there are a bunch of those).

If I join PRICE_LIST to just one table — either SKU_PRICE or CUSTOMER — I get sensible results. It's only when I try and do both at once that it b0rks.

If someone can nudge me in the right direction I'd really appreciate it.

FWIW, I'm using PostgreSQL 9.3.5 on OS X.

roman · Accepted Answer · 2015-01-08 12:21:55Z

6

If I understand your schema, you can do something like

select
    pl.price_list_id,
    pl.price_number,
    pl.name,
    count(distinct sp.sku_id) as "sku_count",
    count(distinct c.customer_id) as "customer_count"
from price_list pl
    left outer join sku_price as sp on sp.price_number = pl.price_number
    left outer join customer as c on c.price_number = pl.price_number
where c.customer_type = 'E' and c.is_active = 'T'
group by pl.price_list_id, pl.price_number, pl.name;

But performance wise I think it would be better to do it like this:

with cte_sku_price as (
     select count(*) as cnt, price_number from sku_price group by price_number
), cte_customer as (
     select count(*) as cnt, price_number
     from customer
     where customer_type = 'E' and is_active = 'T'
     group by price_number
)
select
    pl.price_list_id,
    pl.price_number,
    pl.name,
    sp.cnt as "sku_count",
    c.cnt as "customer_count"
from price_list pl
    left outer join cte_customer as c on c.price_number = pl.price_number
    left outer join cte_sku_price as sp on sp.price_number = pl.price_number

answered Jan 8, 2015 at 12:21

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

That kind of just masks the problem, I think. It still doesn't join properly. I have a price number 41, for example, that has 896 products and no customers, but it's not provided in the output.

roman Over a year ago

@Andrew see my next query and if you want to see those without customers you have to change inner join to left outer join

Andrew Over a year ago

Bingo. That works pretty darn well. Has blanks instead of 0 for no join, but I can live with that.

mlinth Over a year ago

@Andrew, you can use the "COALESCE" function to convert any null values to zeroes i.e. COALESCE(c.cnt,0)...

Collectives™ on Stack Overflow

Joining multiple tables in PostgreSQL, and counting joined table rows

Tables

Query

Problem

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Tables

Query

Problem

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related