Re: Counting unique comma-delimited values in a text field

Lists: Postg토토 핫SQL : Postg토토 핫SQL 메일 링리스트 : 2010-09-29 이후 SFPUG 18:18
From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Counting unique comma-delimited values in a text field
Date: 2010-09-29 17:45:25
Message-ID: 4CA37B35.1020306@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg와이즈 토토SQL : Postg와이즈 토토SQL 메일 링리스트 : 2010-09-29 이후 SFPUG 17:45


Hi list,

I have a table where multiple values are stored in a delimited string
field. Values look like:

Joe
Joe,Frank,John
John,Frank
Frank
Joe,Frank
John

I need to generate aggregate results like:

Joe: 3
Frank: 4
John: 3

The table design in this case is fixed so and this is what I've tried so
far:

SELECT count(theAnswer), theAnswer
FROM (SELECT unnest(string_to_array(answer, ',')) AS theAnswer
FROM Answers) AS theAnswer
GROUP BY theAnswer

This seems to work - any gotchas I might be overlooking or a better way
of finding the totals?

Thanks,

Brian

From: David Fetter <david(at)fetter(dot)org>
To: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Counting unique comma-delimited values in a text field
Date: 2010-09-29 18:18:11
Message-ID: 20100929181811.GB805@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg토토 핫SQL : Postg토토 핫SQL 메일 링리스트 : 2010-09-29 이후 SFPUG 18:18

On Wed, Sep 29, 2010 at 10:45:25AM -0700, Brian Ghidinelli wrote:
>
> Hi list,
>
> I have a table where multiple values are stored in a delimited
> string field. Values look like:

That's not a super great idea. If you need such an interface, make it
a view on top of the underlying normalized tables you'll use to store
the actual data. :)

Whether you actually need that view or not, normalize the data, and
your queries will be a lot easier to manage.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Counting unique comma-delimited values in a text field
Date: 2010-09-30 21:14:35
Message-ID: 4CA4FDBB.1000808@agliodbs.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: sfpug

Brian,

> SELECT count(theAnswer), theAnswer
> FROM (SELECT unnest(string_to_array(answer, ',')) AS theAnswer
> FROM Answers) AS theAnswer
> GROUP BY theAnswer
>
> This seems to work - any gotchas I might be overlooking or a better way
> of finding the totals?

Well, aside from normalizing the data, that's probably as good as it gets.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com

From: Brian Ghidinelli <brian(at)pukkasoft(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Counting unique comma-delimited values in a text field
Date: 2010-09-30 21:17:03
Message-ID: 4CA4FE4F.2040408@pukkasoft.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: sfpug


On 9/30/2010 2:14 PM, Josh Berkus wrote:
> Well, aside from normalizing the data, that's probably as good as it gets.

Thanks Josh - database design in this case is out of my hands so we'll
make do with this. It only occurs in some limited cases.

Brian