MySQL Forums
Forum List  »  Newbie

Re: Problem with SELECT ... COUNT(x) AS y ... WHERE y...
Posted by: Roland Bouman
Date: July 11, 2005 01:18PM

Well, it has nothing to do with the count function itself.
The problem is that you cannot reference a SELECT column alias in the WHERE of that same SELECT expression.

Normally, you could make this work by substituting the alias in the where for the expression you use in the select, sho that the WHERE would read

WHERE COUNT(p.user_id) + COUNT(ap.user_id) < s.target_enroll

but...then you would run into a problem that definitely has to do with the COUNT, or te be exact, with referencing COUNT (or any aggregate function for that matter) as part of the WHERE. You cannot reference an aggregate (such as COUNT, SUM, and friends) like the one in your example in the WHERE because the aggregate is computed *after* the WHERE condition has been processed. Cutting it short, try putting your condition as a HAVING condition.
you query would then read:

SELECT s.study_name
, COUNT( ap.user_id ) AS ap_num
, COUNT( p.user_id ) AS p_num
FROM studies AS s
LEFT JOIN participants AS p ON s.study_id = p.study_id
LEFT JOIN archived_participants AS ap ON s.study_id = ap.study_id
GROUP BY s.study_name,s.target_enroll
HAVING COUNT( p.user_id )+ COUNT( ap.user_id ) < s.target_enroll

By the way I don't know what problem you are trying to solve, but the intention seems to be to select all sturides that did not get enough participants and archived participants according to the target.

If thats the case, you sould almost definitely use COUNT (DISTINCT ), because you only want to count each individual case just once. You don't want to count duplicates caused by the joining process.

Options: ReplyQuote


Subject
Written By
Posted
Re: Problem with SELECT ... COUNT(x) AS y ... WHERE y...
July 11, 2005 01:18PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.