Marketing Cloud -SQL Group BY

Question

I have a table and I need to run some calculations on it for reporting purposes.

I want to add a row that measures counts by that month. And I'm attempting to use the following statement:

SELECT
SubscriberKey,
Month,
COUNT(Score) AS Count_by_Month
FROM Table1
GROUP BY Month, Count_by_Month, Score, SubscriberKey  

I want my table looks like this:

SubscriberKey  Score   Month   Count_by_Month
1234            5      08        2
2345            8      08        2
3456            10     09        1
4567            1      10        1   

Ultimately what I get is a count of that row, which is always 1 and most likely because Marketing Cloud forces me to include the PK in the Select statement, and use it in the GROUP BY.

EDIT: To make things a bit more complicated I need to add a CASE WHEN clause to do the calculations: So I've take the solution before and attempted to modify it like so:

  SELECT
    a.SubscriberKey,
    a.Month,
    b.Score
    FROM Table1 a
    LEFT JOIN (
      SELECT Month,
      COUNT(Score)/(CASE WHEN Score < 7 THEN COUNT(Score) END) AS Count_by_Month
      FROM Table1
      GROUP BY Month
    ) b
    ON a.Month = b.Month 

Even if I were to count the months, the score needing to be less than 7 is forced into the GROUP BY, thus resulting in a row by row result of "1" again.

Answer 1

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.

Answer 2

Take the count_by_month out of the group byclause. Count is any aggregate function .. so when you use it.. you need to group by everything else not in an aggregate function