SQL count problem

cerebis

SQL count problem

Hey lads

I want to be able to count 2 different value's in the same field
and output them.

For example I have an Access DB and in it I have a field name called subcat2.
Populated in this field is either the value Hotel or B&B.

eg.

subcat2

---

HOTEL
HOTEL
HOTEL
HOTEL
HOTEL
B&B
B&B
B&B


I then want SQL to count how many of Hotel values and how many B&B values. So that I can have an

output on a HTML page like :

Hotels (5) B&B (3).

---

my code is as follows:

<%
sql= ""
strsql = ""
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("DBNAME")
strsql="SELECT COUNT(*) FROM TABLE WHERE subcat2='B&B'"
strsql="SELECT COUNT(*) FROM TABLE WHERE subcat2='hotel'"

set conntemp=server.createobject("adodb.connection")
'Without the following ConnectionTimeout setting application fails intermittently.
conntemp.ConnectionTimeout=20
conntemp.open strconn
set rstemp=conntemp.execute(strsql)
If rstemp.eof then
response.write "0"
conntemp.close
set conntemp=nothing
response.end
end if
%>


Hotels (<%response.write(rstemp(0))%>) // counts the hotel's properly
B&B (<%response.write(rstemp(1))%>) //wont count the B&B's

<%
rstemp.MoveNext
' move to the next row in the data set
'loop
' goto the next itteration of the while loop
rstemp.Close
Conntemp.Close
%>


I'm not getting anywhere fast with this and tis wrecking my head!! Anyone got any thoughts??

forbairt

er ... not knowing access as such ...

SELECT COUNT(subcat2),subcat2 as totals FROM TABLE GROUP BY subcat2 order by subcat2

should return 2 rows ... ? with
total and category ...

that said ... that'd be doing it with mysql no idea if it'll work (haven't tried it out)

vito

cerebis wrote:

SQL count problem

<%
sql= ""
strsql = ""
strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("DBNAME")
strsql="SELECT COUNT(*) FROM TABLE WHERE subcat2='B&B'"
strsql="SELECT COUNT(*) FROM TABLE WHERE subcat2='hotel'"

I presume this is ASP or somthing? I only know PHP but it looks like you are overwriting strsql and thats the query you're doing (hence result set for hotel).

You want to do 2 select queries:
strsql_1="SELECT COUNT(*) FROM TABLE WHERE subcat2='B&B'"
strsql_2="SELECT COUNT(*) FROM TABLE WHERE subcat2='hotel'"

Sorry I dont know MS code (what is it ASP or VisualBasic or something?) so cant really help but I'm pretty sure in any language you're overwriting strsql from the first line by redeclaring it in the second.