WPDB for post count on post

The problem you are having is that get_queried_object returns results based on the query that was run.

On author.php, the queried object was an author.

On single.php, page.php, or any custom post type template, the queried object will be the post, not the author.

This means that if you want to use $wp_query->get_queried_object();, you will have to be aware of what kind of object you re grabbing … and possibly do other things if your object is a post or a page.

e.g.

$post = $wp_query->get_queried_object();
$author_id = $post->post_author;
$post_count = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_author="" . $author_id . "" AND post_type="campaigns" AND post_status="publish"");

For term pages, post archive pages, etc., you might need to do such inside of the loop by grabbing the current post object, reading the author, and then grabbing that authors counts.