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.