postgresql - I need to count the number of different rows a word appears in -

postgresql - I need to count the number of different rows a word appears in -

so far have

select word, count(*) (select regexp_split_to_table(coldescription, '\s') word tblcollection ) grouping word order count(*) desc

which makes nice list of of words , how many times appear throughout description column.

what need way display how many times word in row @ to the lowest degree once.

for example, if info was:

hello hello test hello test test test test hi

it show

word count # of rows appears in hello 3 2 test 5 3 hi 1 1

i'm much beginner databases, help appreciated!

a sample table:

create table tblcollection ( coldescription varchar(500) not null primary key);

the sample info is:

"hello hello test" "hello test test test" "test hi"

each string beingness own row.

the main obstacle subquery doesn't preserve info found each instance of word. fixed:

select regexp_split_to_table(coldescription, '\s') word, coldescription tblcollection

now you've got source field listed along each word, , it's matter of counting them:

select word, count(*), count(distinct coldescription) ...

postgresql count