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 |
Thread: | |
Lists: | sfpug |
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 | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2010-09-29 18:18:11 | Re: Counting unique comma-delimited values in a text field |
Previous Message | Jon Asher | 2010-09-23 21:48:50 | Dear Leader T-shirts |