How to use actual row count (COUNT(*)) in WHERE clause without writing the same query as subquery?

Question

I have something like this:

SELECT id, fruit, pip 
FROM   plant 
WHERE  COUNT(*) = 2;

This weird query is self explanatory I guess. COUNT(*) here means the number of rows in plant table. My requirement is that I need to retrieve values from specified fields only if total number of rows in table = 2. This doesn't work but: invalid use of aggregate function COUNT.

I cannot do this:

SELECT COUNT(*) as cnt, id, fruit, pip 
FROM   plant 
WHERE  cnt = 2;

for one, it limits the number of rows outputted to 1, and two, it gives the same error: invalid use of aggregate function.

What I can do is instead:

SELECT id, fruit, pip 
FROM   plant 
WHERE  (
        SELECT COUNT(*) 
        FROM   plant
       ) = 2;

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 COUNT(*) subquery in the given example isn't that big an overhead.

Edit: I do not know why the question is downvoted. The COUNT(*) 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.

Here is the actual query:

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.

Kindly ignore the above query and guide me to right direction from the small example query I posted, thanks.

Answer 1

In MySQL, you can only do what you tried:

SELECT id, fruit, pip 
FROM   plant 
WHERE  (
        SELECT COUNT(*) 
        FROM   plant
       ) = 2;

or this variation:

SELECT id, fruit, pip 
FROM   plant 
  JOIN
      (
        SELECT COUNT(*) AS cnt 
        FROM   plant
       ) AS c
    ON c.cnt = 2;

Whether the 1st or the 2nd is more efficient, depends on the version of MySQL (and the optimizer). I would bet on the 2nd one, on most versions.

In other DBMSs, that have window functions, you can also do the first query that @Andomar suggests.

---

Here is a suggestion to avoid the bottleneck of calculating the derived table twice, once to get the rows and once more to get the count. If the derived table is expensive to be calculated, and its rows are thousands or millions, calculating them twice only to throw them away, is a problem, indeed. This may improve efficiency as it will limit the intermediately (twice) calculated rows to 3:

SELECT  p.*
FROM
    ( SELECT id, fruit, pip 
      FROM   plant 
      LIMIT 3
    ) AS p
  JOIN
    ( SELECT COUNT(*) AS cnt
      FROM   
        ( SELECT 1 
          FROM   plant 
          LIMIT 3
        ) AS tmp
    ) AS c
    ON c.cnt = 2 ;

Answer 2

After re-reading your question, you're trying to return rows only if there are 2 rows in the entire table. In that case I think your own example query is already the best.

On another DBMS, you could use a Windowing function:

select  *
from    (
        select  *
        ,       count(*) over () as cnt
        from    plant
        ) as SubQueryAlias
where   cnt = 2

But the over clause is not supported on MySQL.

old wrong anser below

The where clause works before grouping. It works on single rows, not groups of rows, so you can't use aggregates like count or max in the where clause.

To set filters that work on groups of rows, use the having clause. It works after grouping and can be used to filter with aggregates:

SELECT id, fruit, pip 
FROM   plant 
GROUP BY
       id, fruit, pip 
HAVING COUNT(*) = 2;