How to use Group By with dynamic SQL query in Sql Server

Question

I have a procedure which gets certain information. I have added a field to it which gets number of accounts. For this I have used the "Count" aggregate function to it. Now since Count has been added to it, it should have a group by function too. I am tryin to add Group By to it but cant find the exact syntax to use Group By with Dynamic SQL. Below is the query that is used with the procedure:

DECLARE @SQL  nvarchar(max),@paramlist nvarchar(max) 
SELECT @SQL = 'SELECT awt.AcctID, MIN(CAST(awt.autoEnter AS SMALLINT)) AS AutoApproved                
FROM dbo.AccountWorkflowTask awt JOIN dbo.WorkflowTask wt ON awt.WorkflowTaskID = wt.WorkflowTaskID                
WHERE (wt.TaskID IN (9, 17) AND ReasonIDExit = 1)'

SELECT @SQL=@SQL+')) '                                    
SELECT @SQL=@SQL+'SELECT a.ApplID, acct.AcctID, acct.dtApplication, ai.FName, ai.MName, ai.LName, ai.SSN, a.Email, ao.CompanyName,'                
SELECT @SQL=@SQL+'ao.DBAName, ao.TaxID, acct.AcctNum, acct.AcctAffinityNum, luA.AffinityNum, luA.AffinityName, t.TaskDesc, awt.dtEnter, 
Count(DMA.AcctNum) As NoOfAccounts   FROM   dbo.Applicant a JOIN dbo.APPLICANTACCOUNT aa ON a.ApplID = aa.ApplID'

JOIN 'TO SOME TABLE'

WHERE 'Some Condition'

Now I want to use to Group By clause in order to get the count for NoOfAccounts. I am not able to get the syntax for it. Any help will be appreciated.

Answer 1

I got the answer to my question. I have to use the Group By within the Dynamic Sql as below:

SELECT @SQL=@SQL+ ' GROUP BY a.ApplID, acct.AcctID, acct.dtApplication, ai.FName, ai.MName, ai.LName, ai.SSN, a.Email, ao.CompanyName,                
ao.DBAName, ao.TaxID, acct.AcctNum, acct.AcctAffinityNum, luA.AffinityNum, luA.AffinityName, t.TaskDesc,                
awt.dtEnter, q.QueueDesc, w.WorkflowID, w.WorkflowDesc,                
luO.OriginationID, luO.OriginationDesc, aco.dtCheckOut, aco.UserCheckOut, aco.GUIDCheckout, lts.TaskStageDesc,                
acct.dtApplication,cteAge.Age, asa.SOAApplID, w.WorkflowID, luO.OriginationID, awt.userEnter, cteAutoApprove.AutoApproved,          
acctLam.Market, ful.Request, fun.SourceAccountTypeDescription, fun.FundingStatus, acct.BrokerCode,            
  ai.SSN, ao.TAXID, bup.BusPurposeDesc, ao.BusPurposeOther,                             
  note.Note, note.NoteReasonDesc, note.ReasonCode, aa.RelationshipCode, luRel.RelationshipCodeDesc, Addr.Address1, Addr.Address2, 
  Addr.City, Addr.State, Addr.Zip' 

Answer 2

Based on your comment, you don't need a GROUP BY if you use the OVER clause

Use this instead

Count(DMA.AcctNum) OVER () AS NoOfAccounts