8 Replies Latest reply on May 21, 2007 7:07 AM by DaQuilter

    Count number of entries

    DaQuilter
      My DB lists different failure modes that the user can choose from. I need to develop a chart that reports the number of times each failure mode is chosen. I've attached my current code. Thanks!
        • 1. Re: Count number of entries
          turtle7
          I don't really understand your question...

          are you just asking for what query you should use?

          you probably want something like

          select count(1), getdata.rcscfail
          from getdata
          group by getdata.rcsfail

          that will give you the count for each failure type?
          • 2. Re: Count number of entries
            DaQuilter Level 1
            If I have three different failure modes and the user selects mode 1 - 3 times, mode 2 - 6 times and mode 3 - 10 times. I want to it to show me: mode 1 - 3, Mode 2 - 6, Mode 3 - 10. Right now I can only get it to show me all the modes with 1 count each: (mode 1 - 1, mode 2 - 1, mode 3 - 1) or all the modes with the total: (mode 1 - 19, mode 2 - 19, mode 3 - 19). I can't get it to show me the individual count for each mode. I hope this is clearer than mud....Thanks so much for any help somone can give me.
            • 3. Re: Count number of entries
              turtle7 Level 1
              Did you try the query that I gave you, it sounds like it will give you just what you're looking for?

              select getdata.rcscfail as MODE, count(1) as CNT
              from getdata
              group by getdata.rcsfail
              • 4. Re: Count number of entries
                DaQuilter Level 1
                Yes, I've tried both of your queries with some different options as well and I'm still having issues. The Mode is the same for each entry and the count is 1 for each entry. The count for Carrier error should be 2, Credit Card should be 1 and End User Directed Shipment should be 6. Thanks for your help!!

                This is the current code: Output is below the code.

                <cfquery name="getdata" datasource="#Datasource#">
                SELECT DISTINCT rcscfail
                FROM log_web.reccusser
                GROUP By rcscfail
                </cfquery>
                <cfoutput query="getdata">

                <cfquery dbtype="query" name="getcount">
                SELECT getdata.rcscfail AS MODE, count(1) as CNT
                FROM getdata
                GROUP BY getdata.rcscfail
                </cfquery>

                <td>#getdata.rcscfail#</td>
                <td>#getcount.MODE#</td>
                <td>#getcount.CNT#</td>
                </cfoutput>

                OUTPUT: rcscfail:CARRIER ERROR
                Mode:CARRIERERROR CNT:1
                rcscfail: CREDIT CARD
                Mode: ERROR CARRIER ERROR
                CNT: 1
                rcscfail: END USER DIRECTED SHIPMENT
                Mode: CARRIER ERROR
                CNT: 1
                • 5. Re: Count number of entries
                  turtle7 Level 1
                  ah, OK. so, 2 problems.

                  1. the second query is going off the incorrect first query
                  2. don't put a cfquery inside the cfoutput that is display it

                  so, this is what you want

                  <cfquery name="getdata" datasource="#Datasource#">
                  SELECT rcscfail AS MODE, count(1) as CNT
                  FROM log_web.reccusser
                  GROUP BY rcscfail
                  </cfquery>

                  <table>
                  <cfoutput query="getdata">
                  <tr>
                  <td>#getData.MODE#</td>
                  <td>#getData.CNT#</td>
                  </tr>
                  </cfoutput>
                  </table>
                  • 6. Re: Count number of entries
                    DaQuilter Level 1
                    What you posted makes sense but I'm now getting a "FROM keyword not found where expected" error. This is my current code: I really appreciate your help!

                    <cfquery name="getdata" datasource="#Datasource#">
                    SELECT rcscfail AS MODE, COUNT(1) AS CNT
                    FROM log_web.reccusser
                    GROUP BY rcscfail
                    </cfquery>


                    <body alink="#6c6c6c" link="navy" vlink="#990000">

                    <cfoutput query="getdata">
                    <td>#getdata.MODE#</td>
                    <td>#getdata.CNT#</td>
                    </cfoutput>

                    </body>
                    </html>
                    • 7. Re: Count number of entries
                      turtle7 Level 1
                      what database are you using, perhaps the syntax is a little off - I normally work with Oracle and DB2 and have no experience with Access or MySQL
                      • 8. Re: Count number of entries
                        DaQuilter Level 1
                        I am using Oracle.