Here is your best bet:
Run this query:
SELECT Agency_No, Fund_No, completed, count(*) as totalRecs,
FROM SCO567_568
WHERE (Agency_No = #client.agency_no#)
GROUP BY Agency_No, Fund_No, completed
ORDER BY Agency_No, Fund_No, completed
You'll get results like this:
AGY FUND Completed TotalRecs
418 001 0 4
418 001 1 4
418 534 0 2
418 534 1 1
Now output like this (untested):
AGY Fund TotalRecs Completed
<cfloop from="2" to="#query.recordcount#" index="i" step="2">
<cfset total = totalRecs + myquery.totalRecs[i-1]>
#AGY# #FUND# #Total# #totalRecs#<br>
</cfloop>
+-----------------------------------------------+
Bryan Love
Database Analyst
Macromedia Certified Professional
Internet Application Developer
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-----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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
