|
SQL Count Syntax/Examples
SQL can count items. There are a few variations on the syntax
and some simple rules to remember:
- Any field you want to count here is the simplest syntax
"select count(*) from publishers where state='NY'
- If you count a specific field you must groupby that field or
it won't work:
"select count(city),city from publishers group by city"
- The AS operator allows you to specify a name for the counted
field:
"select count(city) as howmany,city from publishers group by city"
filename=/learn/test/SQLcount1.asp
<HEAD><TITLE>SQLcount1.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select count(*) from publishers where state='NY'")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>
filename=/learn/test/SQLcount2.asp
<HEAD><TITLE>SQLcount2.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select count(city),city from publishers group by city")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>
filename=/learn/test/SQLcount3.asp
<HEAD><TITLE>SQLcount3.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select count(city) as howmany,city from publishers group by city")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>
filename=/learn/test/SQLcount4.asp
<HEAD><TITLE>SQLcount4.asp</TITLE></HEAD>
<HTML><body bgcolor="#FFFFFF">
<%
call query2table("select count(*),city,state from publishers group by city,state")
%>
<!--#include virtual="/learn/test/subdbtable.inc"-->
</BODY></HTML>
The Include file looks like this:
filename=/learn/test/subdbtable.inc
<%
sub query2table(inputquery)
set conntemp=server.createobject("adodb.connection")
conntemp.open "DSN=Student;uid=student;pwd=magic"
set rstemp=conntemp.execute(inputquery)
howmanyfields=rstemp.fields.count -1%>
<table border=1><tr>
<% 'Put Headings On The Table of Field Names
for i=0 to howmanyfields %>
<td><b><%=rstemp(i).name%></B></TD>
<% next %>
</tr>
<% ' Now lets grab all the records
do while not rstemp.eof %>
<tr>
<% for i = 0 to howmanyfields
thisvalue=rstemp(i)
If isnull(thisvalue) then
thisvalue=" "
end if%>
<td valign=top><%=thisvalue%></td>
<% next %>
</tr>
<%rstemp.movenext
loop%>
</table>
<%
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
end sub%>
|