title: How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery?
body: <p>I have something like this:</p>
  
  <pre><code>SELECT id, fruit, pip 
  FROM   plant 
  WHERE  COUNT(*) = 2;
  </code></pre>
  
  <p>This weird query is self explanatory I guess. <code>COUNT(*)</code> here means the number of rows in <code>plant</code> table. <strong>My requirement is that I need to retrieve values from specified fields only if total number of rows in table = 2.</strong> This doesn't work but: <code>invalid use of aggregate function COUNT</code>.</p>
  
  <p>I cannot do this:</p>
  
  <pre><code>SELECT COUNT(*) as cnt, id, fruit, pip 
  FROM   plant 
  WHERE  cnt = 2;
  </code></pre>
  
  <p>for one, it limits the number of rows outputted to 1, and two, it gives the same error: <code>invalid use of aggregate function</code>.</p>
  
  <p>What I can do is instead:</p>
  
  <pre><code>SELECT id, fruit, pip 
  FROM   plant 
  WHERE  (
          SELECT COUNT(*) 
          FROM   plant
         ) = 2;
  </code></pre>
  
  <p>But then that subquery is the main query re-run. I'm presenting here a small example of the larger part of the problem, though I know an additional <code>COUNT(*)</code> subquery in the given example isn't that big an overhead.</p>
  
  <p><strong>Edit: I do not know why the question is downvoted. The <code>COUNT(*)</code> I'm trying to get is from a view (a temporary table) in the query which is a large query with 5 to 6 joins and additional where clauses. To re-run the query as a subquery to get the count is inefficient, and I can see the bottleneck as well.</strong></p>
  
  <p>Here is the actual query:</p>
  
  <pre><code>SELECT U.UserName, E.Title, AE.Mode, AE.AttemptNo, 
     IF(AE.Completed = 1, 'Completed', 'Incomplete'), 
     (
      SELECT COUNT(DISTINCT(FK_QId)) 
      FROM   attempt_question AS AQ
      WHERE  FK_ExcAttemptId = @excAttemptId
     ) AS Inst_Count, 
     (
      SELECT    COUNT(DISTINCT(AQ.FK_QId)) 
      FROM      attempt_question AS AQ
      JOIN      `question` AS Q 
            ON  Q.PK_Id = AQ.FK_QId                      
      LEFT JOIN actions AS A                             
             ON A.FK_QId = AQ.FK_QId
      WHERE     AQ.FK_ExcAttemptId = @excAttemptId
           AND (
                    Q.Type = @descQtn 
                OR  Q.Type = @actQtn 
                AND A.type = 'CTVI.NotImplemented'       
                AND A.IsDelete = @status                 
                AND (
                      SELECT COUNT(*) 
                      FROM   actions 
                      WHERE  FK_QId = A.FK_QId 
                         AND type != 'CTVI.NotImplemented'
                         AND IsDelete = @status
                     ) = 0
               )
     ) AS NotEvalInst_Count,  
     (
      SELECT COUNT(DISTINCT(FK_QId)) 
      FROM   attempt_question AS AQ
      WHERE  FK_ExcAttemptId = @excAttemptId 
         AND Mark = @mark
     ) AS CorrectAns_Count, 
     E.AllottedTime, AE.TimeTaken
  FROM   attempt_exercise AS AE
  JOIN   ctvi_exercise_tblexercise AS E 
      ON AE.FK_EId = E.PK_EId
  JOIN   ctvi_user_table AS U 
      ON AE.FK_UId = U.PK_Id
  JOIN   ctvi_grade AS G 
      ON AE.FK_GId = G.PK_GId
  WHERE  AE.PK_Id = @excAttemptId
  -- AND COUNT(AE.*) = @number --the portion in contention.
  </code></pre>
  
  <p>Kindly ignore the above query and guide me to right direction from the small example query I posted, thanks.</p>