How to count same text values in one field, display total of each (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

NEODARK -> How to count same text values in one field, display total of each (4/10/2007 16:23:36)

Hey guys,

New to the forum, but this is a great little site you have here, props. [:D]

I have a little problem. I'm trying to create a page than can count the total number of equal values within a database column. (sort of like count)

For example the database has 5 fields, one of them is PART. The form submitting to this database has pre-determined values in a drop down list, like type1, type2, type3, type4, type5. All these different types are values that will submit to the "PART" column.

I can't figure out how to create a page that can look at the "PART" column, find how many type1, type2, type3, type4, type5 values are in it, count them then display using "GROUP BY" something like:

PART:

type1 total 5
type2 total 25
type3 total 56
type4 total 8
type5 total 3


Problem is that these are text values, so I can't figure out the correct SQL statement to make "COUNT" and "GROUP BY" work.

I hope this makes sense, thanks for the help in advance :)



BeTheBall -> RE: How to count same text values in one field, display total of each (4/10/2007 17:53:43)

Just off the top of my head, I would say you need 5 separate queries.



NEODARK -> RE: How to count same text values in one field, display total of each (4/11/2007 10:23:23)

Thats what I was hoping to avoid, because that one dB column might have over 20 or 30 different parts so it would mean that many queries :(

Are there any other possible solutions? Here's an example of the database http://www.megaupload.com/?d=UC9A6MK8



thanks guys :)



markhawker -> RE: How to count same text values in one field, display total of each (4/11/2007 12:04:39)

How come you aren't just using: 

SELECT PART, COUNT(*) FROM relation GROUP BY PART

Regards,



NEODARK -> RE: How to count same text values in one field, display total of each (4/11/2007 12:23:36)

quote:

ORIGINAL: markhawker

How come you aren't just using: 

SELECT PART, COUNT(*) FROM relation GROUP BY PART

Regards,


duh, because I'm a noob, and I like over complicating things. :p

Thanks a lot man, that did it. Now onto figuring date ranges to be specified when pulling this, so that only data within the timeframe displays.



NEODARK -> RE: How to count same text values in one field, display total of each (4/11/2007 17:16:45)

Well, I guess I'm still a noob sry guys.

The query as stated above works great, but as I said above, I love to complicate things :p

I have the following statement. (but it won't validate) 

SELECT PART, COUNT(*) FROM TABLENAME AND ( Timestamp BETWEEN #FromDate# AND #ToDate#) GROUP BY REASON


What I'm hoping to accomplish is to exectue the same function as: 

SELECT PART, COUNT(*) FROM TABLENAME GROUP BY PART


But, with the added option for the user to have to ability to input from and to dates. So that only data within those date ranges is counted/displayed.

Thanks for anything else you might think of. I really appreciate the help :)




BeTheBall -> RE: How to count same text values in one field, display total of each (4/11/2007 18:17:08)

Timestamp is a reserved word. Try this:

SELECT PART, COUNT(*) FROM TABLENAME AND ( [Timestamp] BETWEEN #FromDate# AND #ToDate#) GROUP BY REASON



markhawker -> RE: How to count same text values in one field, display total of each (4/11/2007 19:34:15)

Ah, no, that might not work! You need to use: 

SELECT PART, COUNT(*) 
FROM relation 
GROUP BY PART 
HAVING ([Timestamp] BETWEEN #FromDate# AND #ToDate#)



BeTheBall -> RE: How to count same text values in one field, display total of each (4/11/2007 22:14:11)

Josh, I shouldn't have copied and pasted your SQL statement as I didn't notice you had omitted the needed WHERE. I believe what you need is this:

SELECT PART, COUNT(*) FROM TABLENAME WHERE [Timestamp] BETWEEN #FromDate# AND #ToDate#) GROUP BY PART

You can't group by REASON if you didn't select that column in the SELECT portion of the SQL. Also, I am assuming you have created variables named FromDate and ToDate.

Mark, I believe your suggestion will result in an error for not including the specified expression as part of an aggregate function, at least that's what I got when testing a similar statement.



markhawker -> RE: How to count same text values in one field, display total of each (4/12/2007 5:34:53)

quote:

ORIGINAL: BeTheBall

Mark, I believe your suggestion will result in an error for not including the specified expression as part of an aggregate function, at least that's what I got when testing a similar statement.

True, that is correct. Well spotted.



NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 8:48:28)

Thanks again guys :)

I'll work on this today and see how it goes, again, thank you [sm=yupi3ti.gif]



NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 10:14:18)

quote:

ORIGINAL: BeTheBall

Also, I am assuming you have created variables named FromDate and ToDate.


Ok, here's the update. (sorry about the reason thing, that should have been "PART" after GROUP BY)

Anyway, I was able to get the DRW to validate the statement, however I had to modify it like so: 

SELECT PART, COUNT(*) FROM TABLENAME WHERE [TIMESTAMP] BETWEEN '#FromDate#' AND '#ToDate#' GROUP BY PART


In short without those, it wouldn't validate. (I read something about a bug in the DRW not knowing to omit those. I also read I have to go back and change it as you originally had it. However, with no ' I get a syntax error after submitting to the page.

I'm passing the values "FromDate" & "ToDate" from a separate form, that posts to this page (test.asp). Like so:

Form that send the two values: (send.asp) 

<form method="POST" action="test.asp">
  <p>From:  <input type="text" name="FromDate" size="20"> To: 
  <input type="text" name="ToDate" size="20"></p>
  <p><input type="submit" value="Submit" name="B1"></p></Form>


But, I guess I'm just totally off on this as I'm still learning new things every day (asp/db). Because otherwise, test.asp produces no error, so that means I'm not specifying the variables correctly, because It just gives me the "no records returned"

Maybe one day I'll be like this: [8|] <-- he looks smart, doesn't he? haha

EDIT: well, I guess I should specify to request these variables from "send.asp" within "test.asp" shouldn't I :p (reading a bit on the subject right now) I don't know how to specify these on test.asp, yet (but working on it) :)

PS: Here's the example: http://www.megaupload.com/?d=H4HQWU7M


Josh




BeTheBall -> RE: How to count same text values in one field, display total of each (4/12/2007 12:01:48)

It should be this:

SELECT PART, COUNT(*) FROM TABLENAME WHERE [TIMESTAMP] BETWEEN #::FromDate::# AND #::ToDate::# GROUP BY PART

The :: :: tell frontpage the value is from a form field or querystring.



NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 12:18:53)

quote:

ORIGINAL: BeTheBall

It should be this:

SELECT PART, COUNT(*) FROM TABLENAME WHERE [TIMESTAMP] BETWEEN #::FromDate::# AND #::ToDate::# GROUP BY PART

The :: :: tell frontpage the value is from a form field or querystring.


Awesome, thank you :)

(I still had to add the ' to get it to validate, however that gave me no results, but then I edited those out in html to stop the bot from changing them back) it all works great :)

I love all this added interactivity, it's cool stuff



NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 15:12:11)

Ok, last time I bug you promise :p

Everything works (After I remove the ' symbol from the query. If I leave it, whenever I run the query, no records are returned.

Now, the only problem is that when I first open the page, I get the error:

quote:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '[TIMESTAMP] BETWEEN ## AND ##'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for all form fields that are used in the query.


which is meaningless really, because as soon as you run the query everything works. I even tried making the FromDate & ToDate fields use the <% = Date() %> bot, and it makes no difference. The same error pops up (goes away once I run it) I also tried adding defaults to the values, but the initial error remains.

Is there any nifty way to hide that error, something like "On Error Resume Next"?

thanks [8D]



BeTheBall -> RE: How to count same text values in one field, display total of each (4/12/2007 15:54:13)

Yes. Post the code for the page and I will show you a way to hide the results until the dates are know.



Spooky -> RE: How to count same text values in one field, display total of each (4/12/2007 15:55:17)

Ideally, you will use a separate query page and POST it to this DRW page.
That way the results will only be returned as you request them.

eg
page1 contains a form with the selectable values
page2 would be more or less the same as you have now




NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 16:10:51)

quote:

ORIGINAL: Spooky

Ideally, you will use a separate query page and POST it to this DRW page.
That way the results will only be returned as you request them.

eg
page1 contains a form with the selectable values
page2 would be more or less the same as you have now




That's how I was originally trying to get it to work. (trying) but couldn't figure it out, you can see my form in post 11. :)



quote:

ORIGINAL: BeTheBall

Yes. Post the code for the page and I will show you a way to hide the results until the dates are know.


Here it is. (however, I changed some things (started from scratch) so the query is the same, just different table/field names :) 

<html>

<head>
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>

<body>

<form BOTID="0" METHOD="POST" ACTION="DISP_REP_TOT.asp" ID="form1" Name="form1">

<p align="center"><font face="Arial">Enter the desired date range below using 
the format provided.</font></p>

  <div align="center">
    <center>
  <table BORDER="0" height="48" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
    <tr>
      <td height="22" align="right"><font face="Arial"><b>From:  </b>
      </font></td>
      <td height="22">
      <font face="Arial">
      <input NAME="FromDate" size="20" value="<% = Date() %>"><font size="2"> 
      (MM/DD/YY)</font></font></td>
    </tr>
    <tr>
      <td height="18" align="right"><font face="Arial"><b>To:  </b></font></td>
      <td height="18">
      <font face="Arial">
      <input NAME="ToDate" size="20" value="<% = Date() %>"><font size="2"> 
      (MM/DD/YY)</font></font></td>
    </tr>
  </table>
    </center>
  </div>
  <p align="center">
  <input TYPE="submit" value="Submit"><!--webbot bot="SaveAsASP" clientside suggestedext="asp" preview=" " startspan --><!--webbot bot="SaveAsASP" endspan --></p>
  <p> </p>
</form>
<div align="center">
  <center>
<table width="580" border="1" height="0" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
  <thead>
    <tr>
      <td height="13" align="center" width="430" bgcolor="#102963">
      <font face="Arial" color="#FFFFFF"><b>REASON</b></font></td>
      <td height="13" align="center" width="145" bgcolor="#102963">
      <font face="Arial" color="#FFFFFF"><b>Total</b></font></td>
    </tr>
  </thead>
  <tbody>
    <!--webbot bot="DatabaseRegionStart" s-columnnames="REASON,Expr1001" s-columntypes="202,3" s-dataconnection="RHDISP_DSN" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice="REASON" s-menuvalue="REASON" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource s-displaycolumns="REASON,Expr1001" s-criteria s-order s-sql="SELECT REASON, COUNT(*) FROM DISPOSITION WHERE [TIMESTAMP] BETWEEN #::FromDate::# AND #::ToDate::# GROUP BY REASON" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="FromDate=&ToDate=" s-norecordsfound="No records returned, please verify the date format or submit the query." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="../../_fpclass/fpdblib.inc" u-dbrgn1="../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the start of a Database Results region.</font></td></tr>" startspan b-WasTableFormat="TRUE" --><!--#include file="../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT REASON, COUNT(*) FROM DISPOSITION WHERE [TIMESTAMP] BETWEEN #::FromDate::# AND #::ToDate::# GROUP BY REASON"
fp_sDefault="FromDate=&ToDate="
fp_sNoRecords="<tr><td colspan=2 align=left width=""100%"">No records returned, please verify the date format or submit the query.</td></tr>"
fp_sDataConn="RHDISP_DSN"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice="REASON"
fp_sMenuValue="REASON"
fp_iDisplayCols=2
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="17673" --><tr>
      <td height="19" width="430">
      <font face="Arial"> 
      <!--webbot bot="DatabaseResultColumn" s-columnnames="REASON,Expr1001" s-column="REASON" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>REASON<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"REASON")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="10552" --></font></td>
      <td height="19" width="145">
      <p align="center"><font face="Arial">
      <!--webbot bot="DatabaseResultColumn" s-columnnames="REASON,Expr1001" s-column="Expr1001" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Expr1001<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Expr1001")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="13758" --></font></td>
    </tr>
    <!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="65064" --></tbody>
</table>

  </center>
</div>

</body>

</html>


Thanks again guys, I really appreciate your help :)



NEODARK -> RE: How to count same text values in one field, display total of each (4/12/2007 16:39:19)

Well,

sorry for the double post, but I got it working :)

I went back to two forms as Spooky suggested, and by using the query BeTheBall & Mark helped me build, it's up and running. [:D]

Thanks a lot guys, without your help I'd probably be sitting here, still scratching my head while I read trough all the tuts. (hands on exp rocks) Thank you. :)



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375