jackassplus wrote:
lets say I hve the following in the 'fruits' table: Round orange Sunkist orange navel orange strawberry blueberry sunkist orange apple how would I get something like the following: count as c | Fruit type --------------------------------- 4 | orange 2 | berry 1 | apple
Your best solution is to have separate database fields for your 2 levels of detail, say call them "fruit type" and "variety", or maybe other fields depending on purpose.
This is because the organization of fruit is somewhat arbitrary and isn't easily encoded into variety names without kludges. Also, many fruit with similar names are actually very different. So simple textual analysis of the field you have often won't be very useful.
For example, a kiwifruit is a berry, and a strawberry is very different from a blueberry, the first not actually being a berry at all. There are also various citrus which are crosses between oranges and other citrus. There are also stone fruits which are crosses between plums and apricots. Also, "Sunkist" isn't a variety of orange but rather is a brand name used for multiple varieties.
(Yes, members of my family cultivate fruit trees, so I have a lot of second-hand experience with this.)
If you still want to go by textual analysis as you suggest, it will be nontrivial and involve pattern matching for common suffixes where some are separate words and some aren't, and you can do this pattern matching in an extra select-list item which you then group by.
-- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
