• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

HOw Do I count and identify the number of groups in a groups output.

Guest
Oct 03, 2008 Oct 03, 2008

Copy link to clipboard

Copied

I have a query that reads like this..

SELECT s.spec_Sort,s.spec_ID, s.spec_Name, b.bus_Name, b.bus_ID
FROM (tbl_businesses as b INNER JOIN tbl_SpecBusRel as sb ON b.bus_ID = sb.specbus_busid) INNER JOIN tbl_specialties as s ON sb.specbus_specid = s.spec_ID
ORDER BY s.spec_Sort, b.bus_Name

and then I am outputing like this...
<cfoutput query="rsGetSpecialties" group="spec_Name">
<cfif THIS IS WHAT I NEED)></td><td></cfif>
<h2><a class="specialty" href="javascript;" onclick="dwfaq_ToggleOMaticDisplay(this,'#spec_ID#');return document.MM_returnValue">#spec_Name#</a></h2>
<div id="#spec_ID#" style="display:none">
<cfoutput><p><a href="#request.details#?bus_ID=#bus_ID#">#bus_Name#</a></p></cfoutput>
</div>
</cfoutput>

This displays in this manner...
Specialty
dealer
Specialty
dealer1
dealer2
Etc...

What I am trying to do is find out how many specialties there are, figure out where the middle is so I can insert a new table data cell and start a new column.

Basically - two columns with the data divided in half by specialty NOT by the number of dealers.

I hope that makes sense. Of course I am trying to do it w/ as little query action as possible. My only solution may be to have more than one query and the second only finds a list of the sepcialties that contain the dealers and then find the middle one and simply say if current specialty = middleSpecialty then start a new column.

Thanks all,
Chris
TOPICS
Advanced techniques

Views

752

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 03, 2008 Oct 03, 2008

Copy link to clipboard

Copied

1) Remove the nested cfoutput tag. It is redundant.

2) The column you use as cfouput's group attribute must also be in the query's order-by clause.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 04, 2008 Oct 04, 2008

Copy link to clipboard

Copied

To get the number of occurrences of each specialty, do a query of a query

<cfquery dbtype = "query" name = "getNoOfSpecialties">
SELECT spec_Name, count(spec_Name) as noOfOccurrences
FROM rsGetSpecialties
GROUP BY spec_Name
</cfquery>
<cfdump var="#getNoOfSpecialties#">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Oct 04, 2008 Oct 04, 2008

Copy link to clipboard

Copied

Thanks for the replies. I don't need the number of occurances of each specialties - I simply need to know how many specialties there are and then split them in half.

This may be the long way around -but it is what I did...
<cfquery name="rsGetSpecialties" >
SELECT s.spec_Sort,s.spec_ID, s.spec_Name, b.bus_Name, b.bus_ID, b.bus_Pcity, b.bus_PState
FROM (tbl_businesses as b INNER JOIN tbl_SpecBusRel as sb ON b.bus_ID = sb.specbus_busid) INNER JOIN tbl_specialties as s ON sb.specbus_specid = s.spec_ID
ORDER BY s.spec_Sort, b.bus_Name
</cfquery>
<cfset specs = valuelist(rsGetSpecialties.spec_ID)>
<cfquery name="rsListSpecialties">
SELECT spec_ID,spec_Name
FROM tbl_specialties
WHERE spec_ID in(#specs#)
ORDER BY spec_Name
</cfquery>
<cfset middle = int(rsListSpecialties.recordcount/2)>
<cfset specs = valuelist(rsListSpecialties.spec_Name)>
<cfset middle = listgetat(#specs#,middle)>


then displayed like this...
<td valign="top" width="50%">
<cfoutput query="rsGetSpecialties" group="spec_Name">
<h2><a class="specialty" href="javascript;" onclick="dwfaq_ToggleOMaticDisplay(this,'#spec_ID#');return document.MM_returnValue">#spec_Name#</a></h2>
<div id="#spec_ID#" style="display:none">
<cfoutput><a href="#request.details#?bus_ID=#bus_ID#">#bus_Name# (#bus_PCity#, #bus_PState#)</a><br /></cfoutput>
</div>
*****This is the line that does the splitting *****
<cfif spec_Name EQ middle></td><td valign="top" width="50%"></cfif>
</cfoutput>
</td>


As for removing the extraneous cfoutput --> I thought that I needed that in order to show the material within the grouped data. HOw does CF know what data to repeat and what data to not repeat in a grouped output? I will have to look this one up some more.

thanks for the help

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 04, 2008 Oct 04, 2008

Copy link to clipboard

Copied

From what you've explained, you don't have to go to all that trouble to find the number of specialties. Since there is a spec_ID, which is apparently unique, you could just do

<cfquery dbtype = "query" name = "getNoOfSpecialties">
SELECT count(spec_id) as noOfSpecialties
FROM rsGetSpecialties
</cfquery>
<cfdump var="#getNoOfSpecialties#">

You ended with a question about the first cfoutput tag. I wasn't talking about that one. By "nested", I meant the cfoutput tag within the cfoutput tag.


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Oct 05, 2008 Oct 05, 2008

Copy link to clipboard

Copied

Thanks for the query of the query - I forgot all aobut doing that.

As for the nested output - I too was referring to the nested output.

My understanding is that when you used the 'group=' that you needed to put in the second output wrapper in order to get the unique information output without repeating the duplicate information.

i.e. I thought this ..

<cfoutput group="spec_D">
#spec_Name#
#dealer#<Br>
</cfoutput>

would result in
spec1 dealer1
spec1 dealer2
spec1 dealer3

What I want is

spec1 dealer1
dealer2
dealer3

and that would take this..
<cfoutput group="spec_D">
#spec_Name#
<cfoutput>#dealer#<Br></cfoutput>
</cfoutput>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 05, 2008 Oct 05, 2008

Copy link to clipboard

Copied

Do this and see what happens

cfoutput group="spec_D">
#spec_Name#
#dealer#<Br>
</cfoutput>


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Oct 06, 2008 Oct 06, 2008

Copy link to clipboard

Copied

unfortunately all it does is returne

specname
dealer
specname
dealer
specname
dealer

sadly none of the specials that contain multiple dealers - returns multiple dealers :(

It was fun trying something new

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 07, 2008 Oct 07, 2008

Copy link to clipboard

Copied

LATEST
OK. Do you now have what you want?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation