join two different rows in a single row google sql big query

Just remove GROUP BY post_state:

SELECT 
  'all_posts' as post_state,
  SUM(total_threads) as total_posts,
FROM data.table
WHERE post_state IN ('opened', 'completed')