?
Solved

count() function within an SQL query

Posted on 2008-10-03
5
Medium Priority
?
384 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

578 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