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.

2 Answers 2

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.

7
  • 1
    This worked perfectly, great example and thanks for your help!
    – Waystone
    Dec 16, 2018 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, 2018 at 16:44
  • can you edit your question and show a sample of your needs? Dec 17, 2018 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, 2018 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, 2018 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

1
  • 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. Dec 16, 2018 at 16:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .