Displaying number of records depdning on table contents - classic asp

Hi all,

I want to start messing around with KPI's in my system. I know how to do a simple record count (code displayed below).

I have a task management system where my users have a lot of control concerning system behaviour. For instance, when a task is created it gets the status recieved and when it's done it gets the status Completed. However - in between these types of status, the user can freely define as many status types as they want. So what I want to accomplish is this:

I want my code to run through my Task table counting the number of records per task status - i.e. Tasks completed = 58, Tasks Received = 32, Tasks under review = 23, Tasks waiting on repair = 12 etc. I wont know beforehand what type of status's are present in the system (besides Received and completed) and they may change. How do I accomplish this? Current code below which doesn't solve the issue as it oly looks for one type of task.

Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT Count(*) as Numrecords FROM Fejlmelding order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             set objRSM = objconn.Execute(strSQL)
         
Modtaget = (objRSM("NumRecords"))

Open in new window

micambAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
>>However the other types of status are missing

you need to do a loop in your asp codes... like:
<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding group by Status order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             
do while objRSM.eof = false
    Records = (objRSM("Numrecords"))
   response.write "<br>" & Records & "<br>"
   objRSM.movenext
loop
%>

Open in new window

0
 
Ryan ChongCommented:
how's your table design looks like? in general, you need to group by with the status text, like:
SELECT Status , Count(Status ) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 
micambAuthor Commented:
That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected). How do I display the status text next to the number(s)?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ryan ChongCommented:
>>That returns the number 5 which puzzles me as there in that particular database only exists 4 types currently (Received, Completed, See comments & rejected)

if we are querying same source (same table in same database) with same query, it should return the exact number of records, either as you claimed it 4 or 5 records in total.

>>How do I display the status text next to the number(s)?
do you want to do it in query or in asp ?
0
 
micambAuthor Commented:
Just to clarify:

I'm looking for code, that can display thinsg like this (in this particular database):

Recieved = 12
See comments = 3
Completed = 7
Rejected = 3

(And any other status type found in that table)
0
 
micambAuthor Commented:
Query, please - and thank you so much for the assistance so far!
0
 
Ryan ChongCommented:
may i also know what database are you using?
0
 
micambAuthor Commented:
mysql
0
 
Ryan ChongCommented:
see if this is what you need?
SELECT concat(Status, ' = ' , Count(Status )) as Numrecords
FROM Fejlmelding
group by Status
order by Status ASC

Open in new window

0
 
micambAuthor Commented:
We are getting close. This returned:
Completed = 713

However the other types of status are missing. I probably need a loop of some sort? I'll just post the code as it looks now.

<!--#include file="db/database_read.inc"--><%
Dim objconn,objRSM,strSQL

Set objconn = Server.CreateObject("ADODB.Connection")
objconn.Open strConnect

	  strSQL = "SELECT concat(Status, ' = ' , Count(Status )) as Numrecords FROM Fejlmelding group by Status order by Status ASC"
	     set objRSM = objconn.Execute(strSQL)
             
         


Records = (objRSM("Numrecords"))



%>
<br><%= Records %><br>

Open in new window

0
 
micambAuthor Commented:
Absolutely amazing! Thanks a million!
0
 
micambAuthor Commented:
Great solution that solved my problem quickly and efficiently!
0
 
Ryan ChongCommented:
you're welcome glad could make help, cheers
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.