Solved

Counting Query

Posted on 2002-04-12
7
211 Views
Last Modified: 2013-12-24
I'm working on a web based IT knowledbase type app for my personal use. I'm trying to accomplish something I have seen on other websites. I have a DB with two tables. One table has all the different categories and their category id, and the other table has a the actual content, category ID, etc. (Look at the attached code below and look at the CFQUERY pulling out of the KNOWLEDGEBASE table or download a copy of this DB here: http://www.georgelemos.com/glkb.zip)

If you go to http://www.georgelemos.com/categories.cfm you will see the template I am working on. You'll notice the (4) next to each category. What I want to do, is query the KNOWLEDGEBASE table and count how many articles I have stored in the DB for each CATEGORY. Then next to each category have a number displaying how many articles for that CATEGORY are stored in the DB.

I have tried a few different ways but I really came up with nothing that works. Right now all its doing is displaying how many entries are in cetegory 1 (which are 4) I am assuming there's some type of CFLOOP needed which I am really awful with.

If someone has a piece of code available that could help me out I would greatly appreciate it. The code below is what I have running on the page URL listed above.

Thanks!

<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
   Select CATID, CATEGORY
   From CATEGORIES
   Order By CATEGORY
</CFQUERY>



<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
  SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, (SELECT Count(*)
  FROM KNOWLEDGEBASE where KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID) AS CategoryCount
  FROM CATEGORIES;
</CFQUERY>









<HTML>
<HEAD>
 <TITLE>Categories</TITLE>
 <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
 <LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>






<TABLE WIDTH="525" CELLSPACING="3" CELLPADDING="3">
<TR>                                          
<CFOUTPUT QUERY="CATZ">
<TD><A HREF="catpage.cfm?CATID=#CATZ.CATID#">#CATZ.CATEGORY#</A> (#NUMZ.CategoryCount#)<BR>
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>


</BODY>
</HTML>
0
Comment
Question by:gplemos
  • 3
  • 2
  • 2
7 Comments
 
LVL 19

Expert Comment

by:cheekycj
ID: 6936900
<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
  Select CATID, CATEGORY
  From CATEGORIES
  Order By CATEGORY
</CFQUERY>

<cfloop query="CATZ">
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
 SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, COUNT(*) AS CategoryCount
FROM KNOWLEDGEBASE, CATEGORIES where KNOWLEDGEBASE.CATEGORY = CATZ.CATID
AND CATEGORIES.CATID = KNOWLEDGEBASE.CATEGORY
</CFQUERY>
</cfloop>

should work.

Just use the query numz now to display the stuff like:

<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>

CJ
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6936904
oops forgot the ending </cfoutput>:
<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
</cfoutput>

0
 
LVL 1

Accepted Solution

by:
ndintenfass earned 100 total points
ID: 6937437
Looping over a query, though, can be "expensive" overhead.  You can accomplish this in one query like this:

<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
  Select     CATEGORIES.CATID,
                 CATEGORIES.CATEGORY,
               COUNT(KNOWLEDGEBASE.CATEGORY) As CategoryCount
               
  From           KnowledgeBase RIGHT JOIN CATEGORIES ON KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID
 
  GROUP BY      CATEGORIES.CATID,CATEGORIES.CATEGORY
 
  Order By      CATEGORIES.CATEGORY
</CFQUERY>

<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>

<TABLE WIDTH="525" CELLSPACING="3" CELLPADDING="3">
<TR>                                          
<CFOUTPUT QUERY="CATZ">
<TD><A HREF="catpage.cfm?CATID=#CATZ.CATID#">#CATEGORY#</A> (#CategoryCount#)<BR>
</TD>
<CFIF CATZ.CURRENTROW MOD 2 IS 0>
</TR>
<TR>
</CFIF>
</CFOUTPUT>
</TABLE>


</BODY>
</HTML>


OR, if you want only the categories that do have entries in the knowledge base, you can simply have the query be:


  Select     CATEGORIES.CATID,
                 CATEGORIES.CATEGORY,
               COUNT(KNOWLEDGEBASE.CATEGORY) As CategoryCount
  From           CATEGORIES,
                 KNOWLEDGEBASE
  WHERE          KNOWLEDGEBASE.CATEGORY = CATEGORIES.CATID
  GROUP BY      CATEGORIES.CATID,CATEGORIES.CATEGORY
  Order By      CATEGORIES.CATEGORY
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 19

Expert Comment

by:cheekycj
ID: 6937474
>Looping over a query, though, can be "expensive" overhead

true but it really does depend on the data set.

If the categories is under like 500 .. its probably
fine performance wise.

CJ
0
 

Author Comment

by:gplemos
ID: 6937520



Using the code below on URL:
http://www.georgelemos.com/categories2.cfm

I get the error:

"ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'CATEGORY' as part of an aggregate function.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:1) to (8:39)."


Any suggestions?













<CFQUERY NAME="CATZ" DATASOURCE="GLKB">
 Select CATID, CATEGORY
 From CATEGORIES
 Order By CATEGORY
</CFQUERY>

<cfloop query="CATZ">
<CFQUERY NAME="NUMZ" DATASOURCE="GLKB">
SELECT CATEGORIES.CATEGORY, CATEGORIES.CATID, COUNT(*) AS CategoryCount
FROM KNOWLEDGEBASE, CATEGORIES where KNOWLEDGEBASE.CATEGORY = CATZ.CATID
AND CATEGORIES.CATID = KNOWLEDGEBASE.CATEGORY
</CFQUERY>
</cfloop>







<HTML>
<HEAD>
<TITLE>Categories</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<LINK REL="stylesheet" HREF="glcss.css" TYPE="TEXT/CSS">
</HEAD>






<cfoutput query="NUMZ">
#NUMZ.CATEGORY# (#NUMZ.CategoryCount#)<br>
</cfoutput>



</BODY>
</HTML>
0
 
LVL 1

Expert Comment

by:ndintenfass
ID: 6937757
I think you need a GROUP BY clause in the query (see my comment from above about doing it all in one query -- same idea)
0
 

Author Comment

by:gplemos
ID: 6940381
That did the trick. Thank you to both of you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
nginx with multiple websites --need help with phpmyadmin please 5 64
SSL sertificate 5 67
System Analysis 5 59
move expression web site to a new server 13 38
A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 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