SQL count occurrences of a column in some other table

Question

I want to count the occurrences of a column of table A to table B.

This query generates the first table

Table A

select geo_address_id 
from favourite_address    
where rider profile_id = 1;


geo_address_id
--------------
     2
     8
     4

The second table looks like this

Table B

rider_profile_id | geo_addresses| counter
-----------------------------------------
        1              8             3
        1              2             3
        1              6             2
        1              4             2
        1              1             1
        1              5             1

geo_address and geo_address_id are referring to the same thing.

I want to count the occurrences of the geo_addresses_id in the second table and end up with something like that

Table C

geo_addresses | counter
-----------------------
      8
      2
      4

ordered desc

I tried to joined the tables in the geo_addresses but I got some ambiguous errors.

Gordon Linoff · Accepted Answer · 2020-07-13 14:46:04Z

1

You just need join and group by:

select fa.geo_address_id, count(b.geo_address_id)
from favourite_address fa left join
     b
     on a.geo_address_id = b.geo_addresses
where fa.rider_profile_id = 1 
group by fa.geo_address_id;

If you qualify all column references in a query, you will never have a problem with ambiguous column references. So, qualifying column references is a good practice.

answered Jul 13, 2020 at 14:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Markos Tzetzis Over a year ago

It didnt work i am getting ERROR:missinf FROM-clause entry from table fav_geo_addresses. I created a view for fa.

Gordon Linoff Over a year ago

@MarkosTzetzis . . . Th query looks well-formed to me, you just need to be careful about the table names.

Collectives™ on Stack Overflow

SQL count occurrences of a column in some other table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related