Solved

count() function within an SQL query

Posted on 2008-10-03
5
337 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 500 total points
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
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 92

Expert Comment

by:Patrick Matthews
Comment Utility
too slow...
0
 
LVL 3

Expert Comment

by:cnjuguna
Comment Utility
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
Comment Utility
Thanks for your help and the quick response
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now