Solved

count() function within an SQL query

Posted on 2008-10-03
5
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Where on a calculated field 1 31
Convert string date and select older than 30 days from today 2 41
Return Rows as per Quantity of Columns Value In SQL 6 27
SQL to JSON 14 33
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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