?
Solved

Coldfusion RecordCounts

Posted on 2007-08-03
3
Medium Priority
?
345 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:brihol44
3 Comments
 
LVL 14

Assisted Solution

by:Scott Bennett
Scott Bennett earned 400 total points
ID: 19629093
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
 
LVL 10

Accepted Solution

by:
js_vaughan earned 800 total points
ID: 19632568
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
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 800 total points
ID: 19632879

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month15 days, 21 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question