CODEWITHSUNDEEP
sqlgoogle-bigquery
Ayush Sood
Ayush Sood

Reputation: 111

How to create new columns with count values based on the values from another column SQL

I am using Big Query and my table looks like this:

ID Month Values

1234 Aug P
1234 Sept P
3456 Aug D
3456 Sept D
4567 Aug P
4567 Sept D

I want to generate new column for each month and ID containing P,D as column names and values is their count for each ID and Month. Like below:

ID Month P D
1234 Aug 1 0
1234 Sept 1 0
3456 Aug 0 1
3456 Sept 0 1
4567 Aug 1 0
4567 Sept 1 1

Upvotes: 0

Views: 1507

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Below is for BigQuery Standard SQL 

#standardSQL
SELECT id, month, IF(value='P', 1, 0) AS p, IF(value='D', 1, 0) AS d
FROM `project.dataset.table`

you can play with above using dummy data from your question 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1234 id, 'Aug' month, 'P' value UNION ALL
  SELECT 1234, 'Sept', 'P' UNION ALL
  SELECT 3456, 'Aug', 'D' UNION ALL
  SELECT 3456, 'Sept', 'D' UNION ALL
  SELECT 4567, 'Aug', 'P' UNION ALL
  SELECT 4567, 'Sept', 'D' 
)
SELECT id, month, IF(value='P', 1, 0) AS p, IF(value='D', 1, 0) AS d
FROM `project.dataset.table`
-- ORDER BY id

with result 

Row id      month   p   d    
1   1234    Aug     1   0    
2   1234    Sept    1   0    
3   3456    Aug     0   1    
4   3456    Sept    0   1    
5   4567    Aug     1   0    
6   4567    Sept    0   1

In case if you have duplicate rows in your table and need to count values vs. just presenting them (as it is in above query) you can use below example 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1234 id, 'Aug' month, 'P' value UNION ALL
  SELECT 1234, 'Sept', 'P' UNION ALL
  SELECT 1234, 'Sept', 'P' UNION ALL
  SELECT 3456, 'Aug', 'D' UNION ALL
  SELECT 3456, 'Sept', 'D' UNION ALL
  SELECT 4567, 'Aug', 'P' UNION ALL
  SELECT 4567, 'Sept', 'D' 
)
SELECT id, month, COUNTIF(value='P') AS p, COUNTIF(value='D') AS d
FROM `project.dataset.table`
GROUP BY id, month
-- ORDER BY id, month

with result 

Row id      month   p   d    
1   1234    Aug     1   0    
2   1234    Sept    2   0    
3   3456    Aug     0   1    
4   3456    Sept    0   1    
5   4567    Aug     1   0    
6   4567    Sept    0   1

Upvotes: 3

Related Questions