• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

MYSql Question

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
AndySulz
Asked:
AndySulz
  • 9
  • 5
  • 4
  • +1
1 Solution
 
ShehzadCommented:
hi,

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



0
 
rspahitzCommented:
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
 
AndySulzAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
AndySulzAuthor Commented:
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
 
rspahitzCommented:
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
 
AndySulzAuthor Commented:
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
 
ShehzadCommented:
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
 
ShehzadCommented:
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
 
AndySulzAuthor Commented:
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
 
ShehzadCommented:
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
 
rspahitzCommented:
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
 
AndySulzAuthor Commented:
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
 
rspahitzCommented:
Did you check Shehzad's last comment.  That seems to be closer to what you want.
0
 
AndySulzAuthor Commented:
I get the same error.
0
 
aranaCommented:
remove the order by just leave the group by and tell us the results. (also remove the distinct)
0
 
AndySulzAuthor Commented:
I got it guys...

Select count(field) as tmp, field from table group by field order by tmp desc limit 1;
0
 
AndySulzAuthor Commented:
Is it possible to award all three of you some points?
0
 
rspahitzCommented:
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
 
AndySulzAuthor Commented:
i'll see what i can do.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 9
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now