Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

count() function within an SQL query

Posted on 2008-10-03
5
Medium Priority
?
383 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:z018196
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22635540
Try this:

select Field,sum(cnt) from
(select field1 as Field,count(*) cnt from YourTable group by field1
union all
select field2,count(*) from YourTable group by field2
union all
select field3,count(*) from YourTable group by field3
) n
group by Field
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22635558
Hello z018196,

Your data set looks denormalized.  You should fix that.  In the meantime...

1) Make a query, step1, like this:

SELECT field1 AS color FROM SomeTable
UNION ALL
SELECT field2 AS color FROM SomeTable
UNION ALL
SELECT field3 AS color FROM SomeTable

2) Make another query based on it:

SELECT TOP 2 color, COUNT(*) AS Qty
FROM step1
GROUP BY color
ORDER BY COUNT(*) DESC

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22635563
too slow...
0
 
LVL 3

Expert Comment

by:cnjuguna
ID: 22636669
with MySQL you can do something like this:

SELECT IF (a>b or a>c, a, null), IF (b>a,b>c, b, null), IF (c>a, c>b, c, null) FROM (SELECT COUNT(field1) as a, COUNT(field2) as b, COUNT(field3) as c FROM table);

in access you would need to use vba to display the two highest columns.
0
 

Author Closing Comment

by:z018196
ID: 31502831
Thanks for your help and the quick response
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question