Counts Posts ( custom post type ) by Author in author.php

OK, so there are some problems with your code…

1. Your SQL isn’t very safe. It would be much nicer, if you used prepare method:

   $wpdb->get_var(
       $wpdb->prepare(
           "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_author = %d AND post_type = %s AND post_status = %s",
           $curauth->ID, 'post', 'publish'
       )
   );
   

2. You clearly say, that you want to count only posts post_type="post" is the part that is responsible for that. So just remove that part or modify it according to your needs.

3. Be careful counting different post types – remember that attachment (and so on) is also a post type.

4. Use built in functions. It would be much nicer to use count_user_posts.

   <?php $curauth = $wp_query->get_queried_object(); ?>
   <h2>Post Count: <?php echo count_user_posts( $curauth->ID, 'post_type' ); ?></h2>