Postgresql – Need to group by column while ignoring some text format

group bypostgresql

I have the following data.

Sport
--------------
Baseball
Football
Baseball [a]
Football [a] [b]
Basketball

How can I group this to ignore the data in the brackets?

Best Answer

The expression

regexp_replace(the_column, ' +.*$', '')

will remove everything starting with the first space character up until the end of the string. So you just need to group by that expression:

with test_data (sport) as
(
  values 
    ('Baseball'), 
    ('Football'), 
    ('Baseball [a]'), 
    ('Football [a] [b]'), 
    ('Basketball')
)
select regexp_replace(sport, ' .*$', ''), count(*)
from test_data
group by regexp_replace(sport, ' .*$', '')

This will return:

regexp_replace | count
---------------+------
Baseball       |     2
Basketball     |     1
Football       |     2

SQLFiddle: http://sqlfiddle.com/#!15/d41d8/3379

Edit

To deal with legitimate multi-word sports there are two options:

remove everything after the first [: regexp_replace(sport, ' \[.*$', '')
remove all characters that occur between [..] and then trim the result: trim(regexp_replace(sport, '\[.*\]', '', 'g'))

Related Solutions

Need to count records and group count by date on Oracle

Unless I am missing something, your query would be something like this:

select created, count(*) CreatedCount
from yourtable
group by created
order by created;

See SQL Fiddle with Demo

Or if you have a time associated with the date, you can use TRUNC:

select trunc(created), count(*) CreatedCount
from yourtable
group by trunc(created)
order by trunc(created);

See SQL Fiddle with Demo

PostgreSQL – Use Result of GROUP BY as Column Names

If you have a limited number of values that you want to convert into columns, then this can easily be implemented using an aggregate function with a CASE expression:

select user_id,
  sum(case when message_type = 'private' then 1 else 0 end) private,
  sum(case when message_type = 'public' then 1 else 0 end) public
from yourtable
group by user_id

See SQL Fiddle with Demo

PostgreSQL has the ability to use a crosstab() that can be used by installing the tablefunc module. This will perform a similar data transformation from rows into columns. Creating a dynamic version of the query is not a straight forward process, here is a great solution for a dynamic crosstab on StackOverflow.

Best Answer

Related Solutions

Need to count records and group count by date on Oracle

PostgreSQL – Use Result of GROUP BY as Column Names

Related Question