Counting number of like cells in column/db

Results 1 to 3 of 3

Thread: Counting number of like cells in column/db

  1. #1
    Join Date
    Dec 1969

    Default Counting number of like cells in column/db

    I am trying to come up with a script to count how many of a certain item is in a column in a database. For instance how many 5's are in column 1; how many 4's; how many 3's, etc. Any ideas on how to query this info? --Thanks

  2. #2
    Join Date
    Dec 1969

    Default RE: Counting number of like cells in column/db

    SELECT COUNT(*) FROM tablename WHERE columnname LIKE &#039;%1%&#039;<BR><BR>will tell you how many records have a &#039;1&#039; in them.<BR><BR>j<BR>

  3. #3
    Join Date
    Dec 1969

    Default Probably better not in SQL...

    You&#039;d have to make a separate SQL query for each total you wanted:<BR><BR>SELECT Count(*) FROM table WHERE field1 = 5<BR><BR>You could probably build a stored procedure in SQL Server to do this an return either a single record or maybe a recordset.<BR><BR>But why?<BR><BR>If you are going to display all the data anyway, then why not use VBScript to do the counting?<BR><BR>Taking you at your word, let&#039;s say I need to find the counts of 0&#039;s, 1&#039;s, 2&#039;s, 3&#039;s, 4&#039;s, and 5&#039;s in a column I am displaying:<BR><BR>&#060;%<BR>Dim counts(5) &#039; 0 through 5<BR>...<BR>Do Until RS.EOF<BR>&nbsp; &nbsp; column1 = RS("someField")<BR>&nbsp; &nbsp; counts(column1) = counts(column1) + 1<BR>&nbsp; &nbsp; Response.Write "&#060;TD&#062;" & column1 & "&#060;/TD&#062;" ...<BR>&nbsp; &nbsp; ... other fields ...<BR>&nbsp; &nbsp; ...<BR>&nbsp; &nbsp; RS.MoveNext<BR>Loop<BR>...<BR>For i = 0 To 5<BR>&nbsp; &nbsp; Response.Write "The number " & i & " appears " & counts(i) _<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; & " times in column 1.&lt;BR&#062;" & vbNewLine<BR>Next<BR><BR>%&#062;<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