Results 1 to 5 of 5
  1. #1
    Join Date
    May 2014
    Posts
    5

    Question Unanswered: Question on Count

    I have a table and when I run a count againt it it returns a count of 51762 records. When the table is viewed in MySQL Workbench I see that the key value is a biblioitemnumber which is incremented. In some spots, the biblioitemnumber skips (i.e. a record will show 31445 as the biblioitemnumber and then the next record will show 31450).

    I made a new table and inserted records in it from the old table using the biblioitemnumber which was incremented up to 51,513 in the old table. A count of this table gives me 35000 records.

    What I am trying to understand is why the count show 51762 on the old table and only 35000 on the new table. I painfully counted the records in the GUI manually.

    I vaguely remember that a record is not actually deleted when deleted but is only marked not to be shown. Is this the reason I'm seing a count of 51762 records but only 35000 actual records.?

    MySQL database is on Ubuntu.

    Thanks for any information becaused I'm certainly confused in Montana.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Execute the following SQL statements:
    Code:
    SELECT Count(*), Count(biblioitemnumber)
    ,  Min(biblioitemnumber), Max(biblioitemnumber), 'Old'
       FROM OldTableName
    UNION SELECT Count(*), Count(biblioitemnumber)
    ,  Min(biblioitemnumber), Max(biblioitemnumber), 'New'
       FROM NewTableName
    Compare the values. I'm guessing that the skipped ranges in the biblioitemnumber column account for the differences.

    -PatP
    Last edited by Pat Phelan; 06-15-14 at 18:48.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    An auto generated number has ameaning solely within the system. It is a mechanism to provide a unique value for each row in a table. It is not an indication of the number of rows actually in the table. It may be, but its not neccesarily.
    Some auto number implementations allow the developer to specify a start and or interval. Theres lots of reasons why the highest value of an autonumber column is not the same as the actual number of rows...
    it could be rows have been deleted.
    it could be the system allocated a number but a user didnt commit the insertion of a row.

    if you want to find the number if rows in a tabke use one of the aggregate functions such as count.

    select count(mycolumn) as norecords from mytable
    where mycolumn should be the name of a column in the table
    mytable is the name of your table.

    you can use count in an group by expression to count by categories or groups in your table.
    select left(mycolumn,1), count(left(mycolumn,1)) as noingroup
    group by left(mycolumn,1)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    May 2014
    Posts
    5

    Smile Thanks so much for your help!

    Because of the functions that you sent me, I was able to track down the issue and finally rebuild the corrupted table that has been plaguing me for the last month without loosing any records except the one corrupted one.

    I really appreciate you taking the time to help me. Thank you, Thank you for your help and time, Pat.

    Phillip
    Last edited by pponchot; 06-16-14 at 20:21. Reason: Enter the name of the person that I am thanking!

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is amazing how quickly you can fix a problem once you can see it!

    I'm glad that I could help.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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