CODEWITHSUNDEEP
mysqlcount
Bryan
Bryan

Reputation: 1241

MySQL COUNT on WHERE Statement

Pleas help me on why COUNT condition on WHERE statement commits an error, and how could i fix it?.

         SELECT jq.taskqueueid,jq.jobid
           FROM (SELECT p.taskID `curentTaskID`,
                    p.taskName `currentTaskName`,
                    p.processingType `currentProcessingType`,
                    p1.taskID `prevTaskID`,
                    p1.taskName `prevTaskName`,
                    p1.processingType `prevProcessingType`
                  FROM projecttask p
                  LEFT JOIN projecttask p1
                    ON p.sequenceNo=p1.nextTaskSequence
                   AND p.projectID=p1.projectID
                 WHERE p.taskID=18) task
           INNER JOIN taskslogs tl
              ON tl.taskID=task.`prevTaskID`
             AND tl.statusDefinitionID=1
           INNER JOIN jobqueue jq
              ON tl.taskqueueid=jq.taskqueueid
           WHERE COUNT(jq.taskqueueid)=COUNT(tl.taskqueueid)

Upvotes: 0

Views: 207

Answers (1)

Harald Brinkhof
Harald Brinkhof

Reputation: 4455

to use an aggregate function like COUNT() you need to do a grouping of data, if you want to use it as a condition you can't use WHERE for this, since WHERE conditions are considered before aggregation. Use GROUP BY with HAVING instead. (see also http://dev.mysql.com/doc/refman/5.0/en/select.html )

Upvotes: 2

Related Questions