Get Total Count from Advanced Querry With CTE

I have a querry that start with a Temporary table a CTE, I am trying to obtain the count from this query but I get an error, is there a way I can do it?

Hi Lucas,

If you would share the error, that could help people to figure out what is wrong.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Lucas,

If you would share the error, that could help people to figure out what is wrong.

Regards,

Daniel


can you try this:

;WITH 

so a semicolon in front of the WITH

Daniël Kuhlmann wrote:

can you try this:

;WITH 

so a semicolon in front of the WITH


I am trying to get only the count(1)

SELECT COUNT(1) FROM(

WITH ACTIVATION AS(

SELECT      '',
            COUNT (DISTINCT ({Key}.[Id])) AS KEYISSUED,
            COUNT (DISTINCT ({ActivatedKey}.[KeyId])) AS KEYACTIVATED,
            {Key}.[MaxUsage] AS KEYMAX,
            {Key}.[ActualUsage] AS KEYACTUAL
                   
FROM       {Key}
LEFT JOIN {KeySafe} ON {Key}.[Id]={KeySafe}.[KeyId]
LEFT JOIN {KeyHome} ON {Key}.[Id]={KeyHome}.[KeyId]
LEFT JOIN  {ActivatedKey} ON {ActivatedKey}.[KeyId]={Key}.[Id]
LEFT JOIN {Location} ON {ActivatedKey}.[Store_Id]={Location}.[Id]

               
GROUP BY {Key}.[KeyTypeId],{KeySafe}.[ItemSKU], {Key}.[MaxUsage],{Key}.[ActualUsage]
)
SELECT    ACTIVATION.KEYTYPE,
          SUM(ACTIVATION.KEYISSUED),
          SUM(ACTIVATION.KEYACTIVATED),
          ACTIVATION.KEYMAX,    
          SUM(ACTIVATION.KEYACTIVATED * ACTIVATION.KEYACTUAL)

FROM      ACTIVATION
GROUP BY  ACTIVATION.KEYTYPE,ACTIVATION.KEYMAX
ORDER BY  ACTIVATION.KEYTYPE

) AS TOTALCOUNT

Still could you add the ; in front of the WITH

Daniël Kuhlmann wrote:

Still could you add the ; in front of the WITH


Sorry, without being able to test this out, I cannot figure out what is wrong.