CODEWITHSUNDEEP
sqlpostgresqlcount
Diego Quirós
Diego Quirós

Reputation: 977

Count number of rows that have a specific word in a varchar (in postgresql)

I have a table similar to the below: 

id | name    |    direction           |
--------------------------------------
1   Jhon       Washington, DC
2   Diego      Miami, Florida
3   Michael    Orlando, Florida
4   Jenny      Olympia, washington
5   Joe        Austin, Texas
6   Barack     Denver, Colorado

and I want to count how many people live in a specific state:

Washington 2
Florida    2
Texas      1
Colorado   1

How can I do this? (By the way this is just an question with an academic point of view ) Thanks in advance!

Upvotes: 0

Views: 270

Answers (3)

roman
roman

Reputation: 117475

My way do making such a queries is two-step - first, prepare fields you need, second, do you grouping or other calculation. That way you're following DRY principle and don't repeating yourself. I think CTE is the best tool for this:

with cte as (
   -- we don't need other fields, only state
   select
       split_part(direction, ', ', 2) as state
   from table1
)
select state, count(*)
from cte
group by state

sql fiddle demo

If you writing queries that way, it's easy to change grouping field in the future.

Hope that helps, and remember - readability counts! :)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Postgres offers the function split_part(), which will break up a string by a delimiter. You want the second part (the part after the comma):

select split_part(direction, ', ', 2) as state, count(*)
from t
group by split_part(direction, ', ', 2);

Upvotes: 1

Miquel
Miquel

Reputation: 858

Initially I would obtain the state from the direction field. Once you have that, it's quite simple:

SELECT state, count(*) as total FROM initial_table group by state.

To obtain the state, some functions depending on the dbms are useful. It depends on the language.

A possible pseudocode (given a function like substring_index of MySQL) for the query would be:

SELECT substring_index(direction,',',-1) as state, count(*) as total 
FROM initial_table group by substring_index(direction,',',-1)

Edit: As it is suggested above, the query should return 1 for the Washington state.

Upvotes: 0

Related Questions