Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MYSql Question

Posted on 2002-06-05
19
Medium Priority
?
206 Views
Last Modified: 2010-05-02
I know how to find the max value of a Mysql column, but is there a function that will give you the max number of occurences that let say the number 1 occurs in a particular column?

Thanks
0
Comment
Question by:AndySulz
[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
  • 9
  • 5
  • 4
  • +1
19 Comments
 

Expert Comment

by:Shehzad
ID: 7057768
hi,

U can use where clause
as in sql :
select count(column_name) from table_name
where column_name like '1';



0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7057794
or if "1" is part of a string:

Select Count(column_name) as FieldCount
  from tblWhatever
 where column_name like '%1%'

In VB, you can then retrieve this from recordset field rstXXX.field("FieldCount").Value
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7057827
Sorry, I actually wrote the question wrong...  let say my column in the db looks like this...
1
2
1
1
3
2
1
Is there a build in function in sql that will say the maximum number of occurences is 1?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:AndySulz
ID: 7057835
also, is the count(column_name) faster than rs.movelast ... rs.recordcount?  I did increase the points because now it turned in to two questions.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7057874
Actually, I think Shehzad's comment answers your question.  And I think that the count will work without the WHERE clause, but movelast/recordcount will not work unless the WHERE clause is included, in which case the speed is probably a tiny bit faster.
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7057899
With out the WHERE clause I think it will count how many rows of data are in the column...i want it to say that the number 1 occurs the most in my sample dataset....basically like the Mode of the data set.
0
 

Expert Comment

by:Shehzad
ID: 7057918
for this purpose u can include the max(fieldname) instead of
count(fieldname)
i.e.

select max(field_name) from table_name;

---
now if you table data is like this
----
a
a
a
b
b
c
a
b
c

---
then the result will be :
a
0
 

Expert Comment

by:Shehzad
ID: 7057920
for this purpose u can include the max(fieldname) instead of
count(fieldname)
i.e.

select max(field_name) from table_name;

---
now if you table data is like this
----
a
a
a
b
b
c
a
b
c

---
then the result will be :
a

:rsphitz
--------

if u remove the where clause the result will be the total number of rows in the table
where first we required on '1''s count

0
 
LVL 2

Author Comment

by:AndySulz
ID: 7057942
Shehzad's solution counts how many 1's occur...and with out the Where clause it will count the total number of rows in the column.  your solution will find the maximum value, in which I shall run on my sample dataset, I will get back the number 3 since 3 > 1 and 3 > 2 ... I do not want the maximum value, I do want the max number of occurences.  i.e. the mode in math terms.
0
 

Expert Comment

by:Shehzad
ID: 7057962
sorry missed this out

try this one, this will give the maximum occurence on top of the list :
********
select count(field_name), field_name from table_name
group by field_name
order by count(field_name) desc
********
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7057997
I seem to recall someone once doing this by doing a count combined with a DISTINCT command, but I don't remember the exact code.  Does this work or can anyone figure it out?

If may be something like this:

select max(
   count(
      select DISTINCT field_name FROM tblxxx
   )
) from tblxxx
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7058025
i tried...
select distinct count(field), field from table group by field order by count(field) desc limit 1

but I get an Invalid use of group function  I am using the windows version of mysql keep in mind...
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 7058031
Did you check Shehzad's last comment.  That seems to be closer to what you want.
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7058036
I get the same error.
0
 
LVL 11

Expert Comment

by:arana
ID: 7058045
remove the order by just leave the group by and tell us the results. (also remove the distinct)
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7058056
I got it guys...

Select count(field) as tmp, field from table group by field order by tmp desc limit 1;
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7058057
Is it possible to award all three of you some points?
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 300 total points
ID: 7059755
To split points, add a zero-point question in the customer service topic area with a reference to this Q and describe how you'd like the points split.  Glad you got it!
0
 
LVL 2

Author Comment

by:AndySulz
ID: 7059862
i'll see what i can do.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

636 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