Re: how do i count() similar items

In response to jackassplus <jackassplus(at)gmail(dot)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/