[SalesForce] Selecting Anniversary/Birthday in next month SQL

You can do it, and you were close, but you need to separate it into a subquery to get the accurate count you need.

If you include your SubscriberKey inside of the GROUP BY, then you will always have a count of 1 as it is the unique key. You need to make sure to only GROUP BY 'Month' to successfully count the months.

See below example:

SELECT 
a.SubscriberKey,
a.Month,
b.Count_by_Month
FROM Table1 a
LEFT JOIN (
  SELECT Month,
  COUNT(*) AS Count_by_Month
  FROM Table1
  GROUP BY Month
) b
ON a.Month = b.Month 

/* Target: Table1 */
/* Action: Update */

Based on your new edits, I would try the following:

SELECT
  a.SubscriberKey,
  a.Month,
  b.Count_by_Month / CASE WHEN c.Count_by_Month > 0 THEN c.Count_by_Month ELSE 1 END as Count_by_Month
FROM Table1 a
  LEFT JOIN (
    SELECT
      Month,
      COUNT(*) AS Count_by_Month
    FROM Table1
    GROUP BY Month
  ) b ON a.Month = b.Month
  LEFT JOIN (
    SELECT 
      Month,
      COUNT(*) Count_by_Month
    FROM Table1
    WHERE Score < 7
    GROUP BY Month
  ) c ON a.Month = c.Month

  /* Target: Table1 */
  /* Action: Update */

This will give you a second join to collect the counts of those that are with a score less then 7. Please note that I also added a case in the division at the top to prevent any dividing by 0.