Count number of entries

Report · May 14, 2007

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!

Report · May 14, 2007

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?

Report · May 15, 2007

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.

Report · May 15, 2007

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

Report · May 16, 2007

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

Report · May 16, 2007

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>

Report · May 18, 2007

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>

Report · May 18, 2007

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

Report · May 21, 2007

I am using Oracle.

Adobe Community

Count number of entries