title: In SQL, how do I create new column of values for each distinct values of another column?
body: <p>Something like this: <a href="https://stackoverflow.com/questions/34166143/sql-how-to-create-a-value-for-a-new-column-based-on-the-count-of-an-existing-col">SQL How to create a value for a new column based on the count of an existing column by groups?</a></p>
  
  <p>But I have more than two distinct values. I have a variable n number of distinct values, so I don't always know have many different counts I have.</p>
  
  <p>And then in the original table, I want each row '3', '4', etc. to have the count i.e. all the rows with the '3' would have the same count, all the rows with '4' would have the same count, etc.</p>
  
  <p>edit: Also how would I split the count via different dates i.e. '2017-07-19' for each distinct values?</p>
  
  <p>edit2: Here is how I did it, but now I need to split it via different dates.</p>
  
  <p>edit3: This is how I split via dates.</p>
  
  <pre><code>#standardSQL
  SELECT * FROM
  (SELECT * FROM table1) main
  LEFT JOIN (SELECT event_date, value, COUNT(value) AS count 
  FROM table1
  GROUP BY event_date, value) sub ON main.value=sub.value 
  AND sub.event_date=SAFE_CAST(main.event_time AS DATE)
  </code></pre>
  
  <p>edit4: I wish PARTITION BY was documented somewhere better. Nothing seems to be widely written on BigQuery or anything with detailed documentation</p>
  
  <pre><code>#standardSQL
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY event_date, value) AS cnt
  FROM table1;
  </code></pre>