In response to jackassplus <jackassp...@gmail.com>: > lets say I have 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
Organize your data better. If you want to analyze data in the way you describe, then you need to store the data in a way that makes in analyzable. I'm 100% sure that someone can provide you with some query magic that will make the example you provided produce the results you're wanting. I'm also 100% sure that a few weeks or months down the line you find exceptions that will break that solution, and that said solution will never really be reliable. If you're going to analyze fruits by type, then you need a type column. You can then group by type to get count()s. To ensure data integrity, you should probably create a fruit_type table with a unique column that lists the possible types, and then foreign key the fruit_type column in the fruits table to that to ensure nothing funky is entered. An enum for type is another possibility. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
