3 added 580 characters in body
source | link

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.

My other feeling isEDIT: To make things a bit more complicated I need to biteadd a CASE WHEN clause to do the bullet, create another table then joincalculations: 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.

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.

My other feeling is to bite the bullet, create another table then join it.

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 deleted 53 characters in body
source | link

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.

My other feeling is to bite the bullet, create another table then join it.

Any help would be greatly appreciated. Thanks!

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.

My other feeling is to bite the bullet, create another table then join it.

Any help would be greatly appreciated. Thanks!

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.

My other feeling is to bite the bullet, create another table then join it.

1
source | link

Marketing Cloud -SQL Group BY

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.

My other feeling is to bite the bullet, create another table then join it.

Any help would be greatly appreciated. Thanks!