Count each Subcat in all categories 1

[Ecreations]

Hi Guys,
I have seen this done before but i have no clue where.
I have a Recordset pulled from a table that have 8 categories, How can i show how many items in each category
example, I want them to show like this

Homes(12) Apartments(10) Condos(4) Commercial(20) and so on

Any help would be appreciated

Thanks

 

[YNOTU]

See if this helps:

http://www.1keydata.com/sql/sqlcount.html

[tt]

​

 

[Ecreations]

DeCojute,
Select Count will only gives me the number of records for a all the listings in the table and i tried the distinct approach but i kept getting an error
Syntax error (missing operator) in query expression 'Count(Distinct Cat)'.

my SQL was like this

SQL="SELECT Count(Distinct Cat) FROM Links"

 

[onpnt]

what type of databse is it

___________________________________________________________________

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[Ecreations]

now its an access 2000
you can see an example on this link

http://www.refdev.com/tutorials/

 

[onpnt]

The same statement in Jet-SQL would be.

SELECT COUNT(Cat)
FROM Links
Group By Cat;

___________________________________________________________________

http://www.onpntwebdesigns.com

The answer to your ??'s may be closer then you think.
Check out Tek-Tips knowledge bank by clicking the FAQ link at the top of the page

 

[Ecreations]

Nope still wouldnt do it
Thanks anyways onpnt

 

[Tarwn]

The group by keyword used in onpnt's post should have cleared up the issue. Since it hasn't then my only guess would be that it isn't working due to your db design. Is the Links table just a bunch of records with a link field and categroy field or are we looking at something differant?

If the records simply look like:
Table Links:
some field, some field, soe field, Cat

Then onpnt's sql statement should have worked perfectly.
More info please?

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[Ecreations]

Tarwn
I was trying to do the process from one table
the fields are called
ID, category, name, Description, Valid

the category should contain homes,appartments,condos and commercial properties

what i wanted is to display it like this

Homes(20) Apartments(10) Condos(2) and so on

 

[Tarwn]

Ok, then the SQL statement provided by Onpnt should work fine. The only possible change I would consider would be to use the AS keyword after the count statement to give the count field an easily accesible field:

'assuming you have a connection object called conn
Dim sql_count
sql_count = "SELECT category,COUNT(category) AS ttl FROM Links GROUP BY category"
Dim rs
Set rs = Conn.Execute(sql_count)
If Not rs.EOF Then rs.MoveFirst

Dim result_str
Do Until rs.EOF
   result_str = result_str & rs("category") & "(" & rs("ttl") & ") "
   rs.Movenext
Loop

'now output result_str wherever and however many times you want:
Response.Write result_str
Response.Write " all the rest of my page"
Response.Write result_str 'again, now I have it once at top and once azt bottom of page, why not

I added category to the select statement simply because it made it easier. One other addition you may want t make is to add an ORDER BY category to get the list back alphabetically.

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[Ecreations]

That worked like a charm
Thank you very much