CODEWITHSUNDEEP
coldfusion
Yannick
Yannick

Reputation: 3663

Coldfusion Query Object Manipulation

I have a coldfusion recordset like the following:

id    name
1     dog
1     dog
2     cat
2     cat
5     lion

The recordcount is 5 but I want without changing my SQL to retrieve the total unique id (in that case 3) using coldfusion.

Upvotes: 2

Views: 732

Answers (2)

ale
ale

Reputation: 6430

An alternative method would be to get the IDs into a list, de-dupe it, and then count the result.

<cfset idList = valueList(myquery.id) />
<cfset dedupedIDlist = ListDeleteDuplicates(idList) />
<cfset uniqueIDcount = listLen(dedupedIDlist) />

ListDeleteDuplicates():

<cfscript>
/**
 * Case-sensitive function for removing duplicate entries in a list.
 * Based on dedupe by Raymond Camden
 * 
 * @param list      The list to be modified. (Required)
 * @return Returns a list. 
 * @author Jeff Howden ([email protected]) 
 * @version 1, July 2, 2008 
 */
function ListDeleteDuplicates(list) {
  var i = 1;
  var delimiter = ',';
  var returnValue = '';
  if(ArrayLen(arguments) GTE 2)
    delimiter = arguments[2];
  list = ListToArray(list, delimiter);
  for(i = 1; i LTE ArrayLen(list); i = i + 1)
    if(NOT ListFind(returnValue, list[i], delimiter))
      returnValue = ListAppend(returnValue, list[i], delimiter);
  return returnValue;
}
</cfscript>

ListRemoveDuplicates() is another way to do the same thing, using the feature of structures that if you add a key to a struct that already exists it will just be overwritten.

Upvotes: 0

SpliFF
SpliFF

Reputation: 39014

If I understand your question correctly you want to count unique ids? Use a Query of Query:

<cfquery datasource="quackit" name="GetAll">
select * from myTable
</cfquery>

<cfquery dbtype="query" name="GetUnique">
select distinct(id) from GetAll
</cfquery>

#GetUnique.recordCount#

Upvotes: 6

Related Questions