Coldfusion RecordCounts

Hello, I need help with getting recordcounts

I have a main category and sub-category list

My current query is:

<cfquery name="get_categories" datasource="protrade" username="" password="">
      SELECT
          *
      FROM
          t_e_categories t1
       left join t_e_sub_categories t2 on t2.category_id = t1.id_category
    left join t_e_events t3 on t3.sub_cat_id = t2.id_sub_category
    left join product_companies t4 on t4.id_company = t3.comp_id
</cfquery>


My Output is formatted like so:

Main Category
>>Sub Category
>>>>Company Listing
Main Category
Main Category
Main Category

What I'm needing is:


Main Category (recordcount for sub-categories)
>>Sub Category (recordcount company listings under the sub-category)
>>>>Company Listing
Main Category
Main Category
Main Category

Brian
brihol44Asked:
Who is Participating?
 
js_vaughanCommented:
Well I am not sure how you are outputting all of this, so that will change your situation.  For that matter, I don't even see an ORDER BY Category, Subcategory in your query.  From the looks of it, you are pulling in all of your data in a single query, so the best approach might be a few query of queries INSIDE your loop like so:

<!--- Get category counts --->
<cfquery name="get_category_count" dbtype="query">
    SELECT Count(*) AS rc
    FROM get_categories
    WHERE Category = #get_categories.Category#
</cfquery>

<!--- Get SubCategory Counts --->
<cfquery name="get_subcategory_count" dbtype="query">
    SELECT Count(*) AS rc
    FROM get_categories
    WHERE Category = #get_categories.Category# AND SubCategory = #get_categories.Category#
</cfquery>

--------------------

If you are using seperate queries for each stage (a category query, a subcategory query, a company query) then just do something like so:

<cfquery name="get_categories" .... >
    SELECT Category, Count(Category) as count_cat ...
    FROM ...
    GROUP BY Category
    ORDER BY Category
</cfquery>

Then in your output...
<cfoutput query="get_categories">
    #get_categories.Category# (#get_categories.count_cat#)
</cfoutput>

----------------------

If neither of these work (again, i cant see your output code) maybe you can create a set of flags/counters.  The idea might be elementry, but it works.

Above your loop set these variables:
<cfset lastCategory = "">
<cfset lastSubCategory = "">
<cfset lastCategoryCount = 0>
<cfset lastSubCategoryCount = 0>

Then add this inside the top of your loop to check if the category/subcategory has changed from record to record:
<cfif get_categories.Category NEQ lastCategory>
    <cfset lastCategory = get_categories.Category>
    <cfset lastSubCategory = get_categories.SubCategory>
    <cfset lastCategoryCount = 1>
    <cfset lastSubCategoryCount = 1>
<cfelse>
    <cfset lastCategoryCount = lastCategoryCount + 1>
    <cfif get_categories.SubCategory NEQ lastSubCategory>
        <cfset lastSubCategory = get_categories.SubCategory>
        <cfset lastSubCategoryCount = 1>
    <cfelse>
        <cfset lastSubCategoryCount = lastSubCategoryCount + 1>
    </cfif>
</cfif>


If none of these ideas got you in the right direction, just give some more clues as to how you are outputting your code, and additional queries if present.
0
 
Scott BennettManager TechnologyCommented:
The way I usually do this is to ad a column to my category table called "Qty" or something and then write a database job that updates that column on a regular basis (the frequency depends on how often the data changes, I've done some once a day, and other applications once a minute). This way I can just ad the Qty column to my query and display it.
0
 
gdemariaCommented:

You can get "live" counts  using group by and counting the specific Pkey of the child record you're after..

<cfquery name="get_categories" datasource="protrade" username="" password="">
      SELECT t1.categoryName, count(distinct t2.subcategoryID) as subCount
      FROM  t_e_categories t1
        left join t_e_sub_categories t2 on t2.category_id = t1.id_category
      group by t1.categoryName
</cfquery>

<cfquery name="get_subcategories" datasource="protrade" username="" password="">
      SELECT t1.categoryID
                , t2.subcategoryName
                , count(distinct t3.eventID) as eventCount
      FROM t_e_categories t1
       left join t_e_sub_categories t2 on t2.category_id = t1.id_category
       left join t_e_events t3 on t3.sub_cat_id = t2.id_sub_category
    group by 1.categoryID, t2.subcategoryName      
</cfquery>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.