Andrew Peterson wrote:
>
> SELECT Agency_No, Fund_No, count(*) as totalRecs,
> (SELECT COUNT(Completed)
> FROM sco567_568
> WHERE Completed = 1 AND AGENCY_NO
> =#client.Agency_No#) AS totCompleted
> FROM SCO567_568
> WHERE (Agency_No = #client.agency_no#)
>
> This gives the following result:
>
> AGY Fund TotalRecs Completed
> 418 001 8 total 5
> 418 534 3 total 5
It does? Even without a GROUP BY on Agency_No and Fund_No?
> This fourth field, Completed, is displayed in the manner I expect, which
> is to say that it is giving me the total number of records Completed for
> the Agency_Number passed in. But what I need to do is factor in a WHERE
> clause based also on the Fund_No, which is not passed in to the query
> but rather obtained from the value SELECTed from the current row of the
> table. In other words, the total for all the records for "Completed"
> based on the agency_no passed in is 5, but what I want is the Completed
> total for each row (Agency_No of 418 and Fund_No of 001 has 4 of its 8
> total items marked Completed (not 5), and the second row, the total is 1
> (not 5).
SELECT
Agency_No,
Fund_No,
count(1) as totalRecs,
(
SELECT COUNT(1)
FROM sco567_568 b
WHERE Completed = 1
AND a.AGENCY_NO = a.AGENCY_NO
AND a.Fund_No = b.Fund_No
) AS totCompleted
FROM
SCO567_568 a
WHERE
Agency_No = <cfqueryparam ...>
GROUP BY
Agency_No,
Fund_No
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
