Michael, Thanks for your reply. Actually, though your solution is helpful, the problem I am having is that if I place Submitted into the query, the various values that Submitted can take (0, 1, and null) will modify the output so that instead of having one row with 13 Entries, the Group By clause is such that the query returns X number of rows where X equals the number of non-unique values (in this case, three). For example, instead of that first row containing 13 entries, I get three separate rows, one row with 10 entries (Submitted = null), a second row with 1 entry (Submitted = 0), and a third row with 2 entries (Submitted = 1), like so:
Providing Agency ||| Providing Fund ||| totlCount 444 ||| 1 ||| (10 Entries) 444 ||| 1 ||| (1 Entry) 444 ||| 1 ||| (2 Entries) Instead of 444 ||| 1 ||| (13 Entries) My grouping is no longer working. Unless the value of Submitted was identical for all 13 records, the Grouping function that the query is invoking breaks up the values into separate records. I hope I am being clear. Thanks again for any assistance. Regards, Andrew > -----Original Message----- > From: Michael Dorr [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 12:14 PM > To: CF-Talk > Subject: RE: SQL Group By Query Question > > > Well, normally to get out of this conundrum, I'll just fiddle > around with the SQL. SQL won't allow grouping by text or bit > datatypes unfortunately - Andrew's point - so you need a CF solution. > > I have a couple ideas on that, but let me give you a SQL > option first. Use the CAST() function in SQL to change the > text or bit datatype to something that can be grouped. > > SELECT agency_no, fund_no, providingAgency_no, > providingFund_no, count(*) > as totlCount, CAST(Submitted as int) AS Submitted > from sco567_568 > where agency_no = '#client.agency_no#' > and Fiscal_Year = #client.cfy# > group by agency_No, fund_No, providingAgency_No, > ProvidingFUnd_No, CAST(Submitted as int) > order by fund_no, providingAgency_no, providingFund_No > > Let me know if this works. I think it should. > > Michael B. Dorr > eLab Web Application Developer > Owen @ Vanderbilt University > [EMAIL PROTECTED] > > > -----Original Message----- > From: Andrew Peterson [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 8:53 AM > To: CF-Talk > Subject: SQL Group By Query Question > > Hi, > > This one is difficult to explain but I'll give it a shot > anyway. I have a > result set produced from the following query: > > SELECT agency_no, fund_no, providingAgency_no, > providingFund_no, count(*) > as totlCount > from sco567_568 > where agency_no = '#client.agency_no#' > and Fiscal_Year = #client.cfy# > group by agency_No, fund_No, providingAgency_No, ProvidingFUnd_No > order by fund_no, providingAgency_no, providingFund_No > > The result set looks something like this (I am grouping the > output by fund): > > Fund: 1 > *Providing Agency* *Providing Fund* *totlCount* > 444 ||| 1 ||| (13 Entries) > 444 ||| 347 ||| (1 Entry) > 444 ||| 921 ||| (1 Entry) > > Fund: 100 > *Providing Agency* *Providing Fund* *totlCount* > 444 ||| 1 ||| (7 Entries) > 492 ||| 1 ||| (1 Entry) > > There is also yes/no field in this table called Submitted > which indicates > whether the record has been submitted. I cannot check for it > in the query > above because it would ruin my grouping and totals. I need to > determine if > all of the entries in each row above have a value of yes in > this field. If > they do, that row is completed and I need to indicate that to > the user. For > example, if all 13 entries in the first row above have a Yes in the > Submitted field, I would place a check mark or some other > indicator next to > it. Otherwise, it will not. Is there any way to do this? > > Thanks in advance for any assistance. > > Andrew > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
