Cannot use the count() function in a query containing subquery-CodePudding

I have the below query working fine.

SELECT
   (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS = 'NEW') AS DATE1,
   (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS= 'OLD') AS DATE
FROM TABLE1 A
WHERE 
   A.STATUS ('NEW') OR 
   A.ID IN (
      SELECT DISTINCT ID 
      FROM LOGTABLE 
      WHERE LOG_DATE BETWEEN @DATETIME AND @DATETIME AND STATUS = 'OLD'
  )

Now I wanted to count the MAX(LOG_DATE).

Below is what I have tried.


SELECT
   COUNT((SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS = 'NEW')) AS DATE1,
   COUNT((SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS= 'OLD')) AS DATE
FROM TABLE1 A

WHERE 
   A.STATUS ('NEW') OR 
   A.ID IN (
      SELECT DISTINCT ID 
      FROM LOGTABLE 
      WHERE LOG_DATE BETWEEN @DATETIME AND @DATETIME AND STATUS = 'OLD'
   )

It resulted in a below error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

CodePudding user response：

I think filtering the Raw data and after that applying, the count would be good option,

;WITH CTE AS (SELECT
   (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS = 'NEW') AS DATE1,
   (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS= 'OLD') AS DATE
FROM TABLE1 A
WHERE 
   A.STATUS ('NEW') OR 
   A.ID IN (
      SELECT DISTINCT ID 
      FROM LOGTABLE 
      WHERE LOG_DATE BETWEEN @DATETIME AND @DATETIME AND STATUS = 'OLD'
  ))

SELECT COUNT(DATE1),COUNT(Date)   FROM CTE