Getting a column with count from a nested query

Viewed 6

as a newbie in SQL I am lost regarding nested queries.

I am trying to achieve the following: getting a table grouped by month with a count from all values of a column, then the same count filtered by status.

So for instance in January I could have the following result:

Jan 22 Count= 100 Count with Status filter= 57

I tried several variations along these lines:

SELECT
FORMAT ( [CreatedDate.table2] , 'yyyyMM' ) as create_month,
RecordTypeName__c,
count(*) as count_all,
count_filtered

FROM
      (SELECT 
FORMAT ( [CreatedDate] , 'yyyyMM' ) as create_month,
RecordTypeName__c,
Count(*) AS count_filtered
FROM DM_AccessNoAgg.DimLead
WHERE [CreatedDate] >= '2022-01-01'
      AND [Status]='Qualifiziert'
GROUP BY RecordTypeName__c,FORMAT ( [CreatedDate] , 'yyyyMM' )
      ) 

Basically I am using the same value in both cases, just that the second count has to be filtered. What's the best method to get this done?

Thanks for your help!

Pauline.

0 Answers
Related