CODEWITHSUNDEEP
sqlgoogle-bigquery
daniel_moura
daniel_moura

Reputation: 13

Merge, Combine, Join 2 different rows in 1 single row Google SQL | Big Query

I have the following table:

| post_state | total_posts |
| opened     | 1000        |
| completed  | 2000        |

I'm using this query to get the above result:

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

However, I would like to combine both rows in a single one, something like this:

| post_state | total_posts |
| all_posts  | 3000        |

How am I able to output this in Google SQL? I'm new to SQL world, happy if you could help me out :)

Thanks in advance for the assistance.

Upvotes: 1

Views: 106

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

Another option

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

Upvotes: 1

Sergey Geron
Sergey Geron

Reputation: 10232

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')

Upvotes: 1

Related Questions