Solved

count() function within an SQL query

Posted on 2008-10-03
5
362 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
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 92

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 92

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 26 68
SQL Insert parts by customer 12 42
TSQL Query Into Specific XML Format w/ Multiple Groupings 6 33
SQL trigger 5 21
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​

840 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