1

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.

0

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.

  • 1
    This worked perfectly, great example and thanks for your help! – Waystone Dec 16 '18 at 18:24
  • So for simplicity sake I dumbed the questioned down. But I'm encountering the same error when I try to make the equation more complex essentially adding a CASE WHEN clause to the count when a different field is needed, I then have to add that field into the GROUP BY thus causing the whole error again. – Waystone Dec 17 '18 at 16:44
  • can you edit your question and show a sample of your needs? – Gortonington Dec 17 '18 at 16:47
  • Added the new question under the original question, which was wonderfully answered for anyone looking for an answer. Thanks again. – Waystone Dec 17 '18 at 17:02
  • 1
    Again, this worked perfectly. Just so I understand: you're creating temporary values here with "b.count_by_month" & c."count_by_month"? For some reason I didn't think this was possible in SF. The only thing I added here was a CAST AS DECMIAL and changed the resulting field to decimal. Thanks again! – Waystone Dec 17 '18 at 18:48
1

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

  • This will still produce a '1' in each column though as the group by will essentially assign the Count function 3 primary keys (Month, Score, Subscriberkey) which will likely be a unique value for each data row. – Gortonington Dec 16 '18 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.