count() function within an SQL query
Posted on 2008-10-03
I have a question regarding the count() function within an SQL statement. I have a table that has 3 fields within it that have similar data. The fields are populated from options on a form. Here is a example of what the data looks like in the DB.
field1 field2 field3
red green red
red yellow green
red red green
blue red red
I'm wondering if there is a way to use the count() function across fields. I would like to know the top 2 values within the 3 fields. For this data set, I would get red=7 and green=3. As of now, I wrote a routine that queries each field and then dumps the data into a table. I then query that table using the count() function to get the top 2. Is there way to use this logic within a query?