Postgresql select count with join

Question

I have two tables:

CREATE TABLE stores (
stores_id varchar PRIMARY KEY,
owner_id varchar
);

CREATE TABLE sets (
sets_id varchar PRIMARY KEY,
stores_id varchar not null,
owner_id varchar not null,
item_id varchar not null,
);

How do I make a request that shows the number of items on the sets in stores? With selection by owner.

For example:

select
stores.*,
count(sets.item_id)
from stores
LEFT OUTER JOIN sets on stores.owner_id = sets.owner_id
where
stores.owner_id = 'e185775fc4f5'
GROUP BY stores.owner_id;

Thank you.

Please edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. (edit your question - do not post code or additional information in comments) — user330315
– user330315, Commented Jun 6, 2018 at 12:04

SuperSecretAndNotSafeFromWork · Accepted Answer · 2018-06-06 12:11:36Z

1

I think you'd need to join on both the store and the owner, then COUNT(DISTINCT item_id)

select
st.owner_id, 
st.stores_id, 
count(distinct se.item_id)
  from stores st left join
    sets se
       on st.owner_id = se.owner_id
       and st.stores_id = se.stores_id
           group by st.owner_id, st.stores_id;

That will give a table that shows the owner, the store, then the number of items

answered Jun 6, 2018 at 12:11

SuperSecretAndNotSafeFromWork

1,3012 gold badges17 silver badges34 bronze badges

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

1 Comment

Sergey Kozlov Over a year ago

Yes, its work! That i was add where st.owner_id = 'e185775fc4f5'

Gordon Linoff · Accepted Answer · 2018-06-06 11:55:06Z

1

Is this what you want?

select st.stores_id, count(se.item_id)
from stores st left join
     sets se
     on st.owner_id = se.owner_id
where st.owner_id = 'e185775fc4f5'
group by st.stores_id;

answered Jun 6, 2018 at 11:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Sergey Kozlov Over a year ago

Collectives™ on Stack Overflow

Postgresql select count with join

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related