Bill Software Engineer
Bill Software Engineer

Reputation: 7782

Get the Count of a SubQuery

I need to count the result of some SQL query which I have no control over. My idea is to wrap the inner SQL with a wrapper count query. The outer SQL is quite simple, here is what I got:

select count(*) from ( x ) as CountQuery

where x is whatever the inner SQL goes. The problem with this is some query would crash, namely IF ELSE END.

How do I craft a wrapper SQL that will successfully wrap around any SQL?

Here is an example of one of the inner SQL I need to count:

IF null <> 'PIPELINE_STAGE'
BEGIN
WITH cnts AS
(
SELECT 
       o.[OPPORTUNITY_ID]
      ,[OPPORTUNITY_NAME]
      ,[OPPORTUNITY_DETAILS]
      ,[IMAGE_URL]
      ,OPPORTUNITY_VALUE
      ,[PROBABILITY]
      ,[BID_CURRENCY]
      ,[BID_AMOUNT]
      ,[BID_TYPE]
      ,[BID_DURATION]
      ,[FORECAST_CLOSE_DATE]
      ,o.[CATEGORY_ID]
      ,c.CATEGORY_NAME
      ,c.BACKGROUND_COLOR
      ,o.[PIPELINE_ID]
      ,o.[STAGE_ID]
      ,[OPPORTUNITY_STATE]
      ,[RESPONSIBLE_USER_ID]
      ,u.[First_Name]
      ,u.[Last_Name]
      ,o.[VISIBLE_TO]
      ,o.VISIBLE_TEAM_ID
      ,o.[DATE_CREATED_UTC]
      ,o.[DATE_UPDATED_UTC]
      ,o.OWNER_USER_ID
      ,o.IMPORT_ID
      ,Follow_id
       ,ROW_NUMBER() OVER( ORDER BY CASE WHEN @sortOrder = 'OPPORTUNITY_NAME' THEN OPPORTUNITY_NAME END,
  CASE WHEN @sortOrder = 'RESPONSIBLE_USER' THEN ISNULL(u.[FIRST_Name], 'zz') END,
  CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE' THEN Forecast_Close_Date END,
  CASE WHEN @sortOrder = 'DATE_CREATED' THEN o.[DATE_CREATED_UTC]  END,
  CASE WHEN @sortOrder = 'FORECAST_CLOSE_DATE_DESC' THEN Forecast_Close_Date END DESC,
  CASE WHEN @sortOrder = 'DATE_CREATED_DESC' THEN o.[DATE_CREATED_UTC]  END DESC
       ) as intRow, 
     COUNT(o.INSTANCE_ID) OVER() AS Count
  FROM [Insightly].[dbo].[Opportunity] o with (nolock)
  LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
  on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
  ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
  ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
AND (@importId IS NULL OR o.IMPORT_ID = @importId)
AND OPPORTUNITY_STATE in @opportunityState)
SELECT * FROM cnts
WHERE intRow BETWEEN @skip AND @take
END
ELSE
BEGIN
WITH cnts AS
(
SELECT 
       o.[OPPORTUNITY_ID]
      ,[OPPORTUNITY_NAME]
      ,[OPPORTUNITY_DETAILS]
      ,[IMAGE_URL]
      ,OPPORTUNITY_VALUE
      ,[PROBABILITY]
      ,[BID_CURRENCY]
      ,[BID_AMOUNT]
      ,[BID_TYPE]
      ,[BID_DURATION]
      ,[FORECAST_CLOSE_DATE]
      ,o.[CATEGORY_ID]
      ,c.CATEGORY_NAME
      ,c.BACKGROUND_COLOR
      ,o.[PIPELINE_ID]
      ,o.[STAGE_ID]
      ,[OPPORTUNITY_STATE]
      ,[RESPONSIBLE_USER_ID]
      ,u.[First_Name]
      ,u.[Last_Name]
      ,o.[VISIBLE_TO]
      ,o.VISIBLE_TEAM_ID
      ,o.[DATE_CREATED_UTC]
      ,o.[DATE_UPDATED_UTC]
      ,o.OWNER_USER_ID
      ,o.IMPORT_ID
      ,Follow_id
       ,ROW_NUMBER() OVER( ORDER BY ISNULL(p.Pipeline_Name, 'zz'), ISNULL([Pipeline.Stage].Stage_Order, 999)
       ) as intRow, 
     COUNT(o.INSTANCE_ID) OVER() AS Count
  FROM [Insightly].[dbo].[Opportunity] o with (nolock)
  LEFT JOIN [Insightly].[dbo].[Reference.Category] c with (nolock)
  on o.CATEGORY_ID = c.CATEGORY_ID AND c.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User] u WITH (NOLOCK)
  ON u.USER_ID = o.RESPONSIBLE_USER_ID AND u.INSTANCE_ID = @instanceId
  LEFT JOIN [Insightly].[dbo].[User.Follow] uf WITH (NOLOCK)
  ON (o.OPPORTUNITY_ID = uf.OPPORTUNITY_ID AND uf.USER_ID = @currentUserId AND uf.INSTANCE_ID = @instanceId)
LEFT OUTER JOIN [Pipeline.Stage] with(nolock) ON o.PIPELINE_ID = [Pipeline.Stage].PIPELINE_ID AND o.STAGE_ID = [Pipeline.Stage].STAGE_ID
LEFT OUTER JOIN [Pipeline] p with(nolock) ON o.PIPELINE_ID = p.PIPELINE_ID AND p.INSTANCE_ID = @instanceId
WHERE o.INSTANCE_ID = @instanceId AND TEMPORARY = 0 
AND (@userId IS NULL OR [RESPONSIBLE_USER_ID] =@userId) AND (@categoryId IS NULL OR c.CATEGORY_ID = @categoryId)
AND (@importId IS NULL OR o.IMPORT_ID = @importId)
SELECT * FROM cnts
WHERE intRow BETWEEN @skip AND @take
END

Upvotes: 1

Views: 62

Answers (1)

yk11
yk11

Reputation: 768

You can create a temp table based on the query and then count the rows in it.

CREATE TEMPORARY TABLE my_results
<whatever query you have>;

SELECT COUNT(*) FROM my_results;

Upvotes: 1

Related Questions