Jocheem (My apologies if I murdered your name) is much better at this. But
I think I've learned from him, you need to do something like:
SELECT Agency_No AS outerNO, Fund_No, count(*) as totalRecs,
(SELECT COUNT(Completed)
FROM sco567_568
WHERE Completed = 1 AND AGENCY_NO
=OuterNO) AS totCompleted
FROM SCO567_568
WHERE (Agency_No = #client.agency_no#)
The idea is that you need a statement in the included select that joins it
to the outer select record by record. Otherwise, it is operating completely
independently and you are getting the count of ALL completed records
regardless of FUND.
HTH
--------------
Ian Skinner
Web Programmer
BloodSource
Sacramento, CA
-----Original Message-----
From: Andrew Peterson [mailto:[EMAIL PROTECTED]
Sent: Monday, July 28, 2003 12:01 PM
To: CF-Talk
Subject: [sql] grouping
Hi
I've got a rather simple query (SQL Server 2000) and its result set
looks like this:
AGY FUND TotalRecs
418 001 8
418 534 3
I'm basically selecting a couple of fields and doing a grouping for the
third field (COUNT(*) AS totalRecs). This works fine. The user, however,
wants a sub grouping, depending on other fields in the result set, and I
can't figure out how to do it. Basically, they want their result set to
look like this:
AGY Fund TotalRecs Completed
418 001 8 total 4
418 534 3 total 1
The added field, Completed, is a yes/no field. I can incorporate this
field into the result set, by doing this SQL:
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
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).
Is there any way finagling I can do to this query to get the results I
want, or perhaps another way to present the data in a row-based format
to achieve the same results? I actually told the user that I didn't
think it could be done, but it would be so sweet if it could. Any ideas?
Thanks in advance.
Sincerely,
Andrew
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
