How to return majority string

Results 1 to 3 of 3

Thread: How to return majority string

  1. #1
    coonabeast(at) Guest

    Default How to return majority string

    Is there any way to draw information from the database that is seen the majority of the time...for example if I had a table that looked like this...<BR><BR>tblJohnsGrades<BR>[Assignment, Grade]<BR>[Chapter 1, 79]<BR>[Chapter 2, 79]<BR>[Chapter 3, 83]<BR>[Chapter 4, 79]<BR><BR>Would there be any way to have a SQL command that returned &#039;79&#039; because it is the string seen most often in the column Grade???<BR><BR>PS I know that what is above is not correct SQL form, I am using an mdb file, I just wanted to set up a matrix to explain this to you.

  2. #2
    Join Date
    Dec 1969

    Default RE: How to return majority string

    You could try using the count() argument as part of your string<BR><BR>Select Yadda from YaddaYadda where count(yadda) &#062; MyNumber<BR><BR>or something along those lines<BR><BR>mj

  3. #3
    Join Date
    Dec 1969

    Default Easiest way...

    SELECT TOP 1 Count(Grade) AS Occurrences, Grade<BR>FROM tblJohnsGrades <BR>GROUP BY Grade<BR>ORDER BY Count(Grade) DESC<BR><BR>How it works:<BR><BR>First, all the Grade&#039;s with the same value are grouped together.<BR>Count(Grade) gets a count of the number in each group.<BR>ORDER BY says put the count with the highest number first (DESCending order)<BR>TOP 1 says only give me the first record in the result.<BR><BR>NOTE: If there are any ties, TOP 1 will just throw them away and keep one (arbitray) result. TOP 1 is, of course, optional.<BR><BR>So you do:<BR><BR>&#060;%<BR>Set RS = someConnection.Execute("...that SQL...")<BR>MostCommonGrade = RS("Grade")<BR>Occurrences = RS("Occurrences")<BR>RS.Close<BR>Response.Write "The most common grade was " & MostCommonGrade & " which occurred " & Occurrences & " times."<BR>%&#062;<BR><BR>Try the query in Access&#039;s query analyzer before committing to ASP.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts