group function in coldfusion

[silviasalsa]

Hello does anyone know how to use the group function in coldfusion
my tables that out-puts to a html
is outputing multiple CEENO

for example CEENO
11111
11111
11111
11111

someone here said to use the group function in coldfusion
do you know where i can put it?




<table border="2" cellpadding="2" cellspacing="0">
<tr>
<th>Record Number</th>
<TH>PEA_id</TH>
<th width="120">CEENO</th>
<TH>Address</TH>

<TH>ZipCode</TH>

<th>AdvisoryDate</th>






</tr>

<cfloop query="getthecases">


<tr bgcolor="<cfif currentrow mod 2>GHOSTWHITE<cfelse>WHITE</cfif>">
<td>#CurrentRow#</td>
<td>#PEA_id</td>
<Td>#CEENO#</td>
<td>#ST_NO# #ST_initial# #st_dir#</td>
<td>#ZipCode#</td>





<td>#dateformat(ADVISORY_DATE,"mm/dd/yyyy")#</td>









</tr>
</cfloop>

</table>

[insuractive]

The Group functionality built into the <Cfoutput> tag works something like this:

<!--- CFOUTPUT LOOP 1 (UNIQUE VALUES OF colA) --->
<cfoutput query="myQuery" group="colA">
<p>#colA#</p>

<!--- CFOUTPUT LOOP 2 (all records associated with unique ColA value)
<cfoutput>
#colB#
</cfoutput>

</cfoutput>

The outer cfoutput block (the one with the group="" attribute) will loop once for every unique value of the column you specify in the group attribute (assuming the values are ordered by ColA). The inner <cfoutput> block will loop for each row in the table that match that unique value of ColA.

TABLE:
ColA, ColB, ColC
A, 1, 0
A, 2, 0
B, 0, 0
C, 3, 1

Using the code above, you would see an output like this:

<p>A</p>
1
2
<p>B</p>
0
<p>C</p>
3

Hope that helps illuminate the problem. The official CF documentation can probably give you a little more indepth explaination.

[silviasalsa]

is this similar to group by in sql?

eliminating redunacies right?


where in my code should this be group be in. ?

Thanks

[Dan_Bracuk]

quote:

---

Originally posted by: silviasalsa
is this similar to group by in sql?

eliminating redunacies right?

Thanks

---

wrong

The group by clause in sql is used in conjunction with aggregates, like count, sum, etc. The group attribute in cfquery enables you fine tune the way you display your data.

[silviasalsa]

thanks
how and where do i place the group function on my code below?

<table border="2" cellpadding="2" cellspacing="0">
<tr>
<th>Record Number</th>
<TH>PEA_id</TH>
<th width="120">CEENO</th>
<TH>Address</TH>

<TH>ZipCode</TH>

<th>AdvisoryDate</th>






</tr>

<cfloop query="getthecases">


<tr bgcolor="<cfif currentrow mod 2>GHOSTWHITE<cfelse>WHITE</cfif>">
<td>#CurrentRow#</td>
<td>#PEA_id</td>
<Td>#CEENO#</td>
<td>#ST_NO# #ST_initial# #st_dir#</td>
<td>#ZipCode#</td>





<td>#dateformat(ADVISORY_DATE,"mm/dd/yyyy")#</td>









</tr>
</cfloop>

</table>

[Dan_Bracuk]

There is no group function. There is a group attribute in cfoutput. You are using cfloop instead of cfoutput so it wouldn't apply.

You have also not explained what it is you want to group and why. Those are rather significant details.