Please help with COUNT

[binto]

I am trying to count the number of items in a shopping category.
I have read as much as possible but can't find a suitable solution.
Will appreciate your assistance.

With the present code (below), I can get the count but the display is not working.

What I want;
wine (4) juice(6) beer (27) whisky(23) numbers are arbitrary

No what I get:
If I click on wine, I get wine (4) juice(4) beer (4) whisky (4)
If I click on beer, I get wine (27) juice(27) beer (27) whisky (27)

I guess I am not properly associating the cat names with the count.

$selQuery4 = "SELECT DeptName FROM tbl_product WHERE id_company='$idcompany' group BY DeptName";
$selResult4=kal_query($selQuery4);
$subQuery4="SELECT COUNT(*) from tbl_product WHERE DeptName like '%$caption%' and id_company='$id_company' and DeptName='$DeptName'group by DeptName,id_product";
$subResult4=kal_query($subQuery4);

$compcount=0;
while($row4=newFetch($selResult4))
{

//$id_product=$row->id_product;
$id_product=$row4->id_product;
$id_company=$row4->id_company;
$DeptName=$row4->DeptName;
$id_dept=$row4->id_dept;

	$count = kal_num_rows($subResult4);

	  		$valListProduct5 .="<TD align=left bgColor=white valign=top><a href='category.php?caption=".rawurlencode($DeptName)."&DeptName=".rawurlencode($DeptName)."' class=prod_link>$DeptName ($count)</a></TD></TR>";

Script is using FastTemplate.
Thank you

[chyan]

it happen because your query just count one product which you click at it

$subQuery4="SELECT COUNT(*) from tbl_product WHERE DeptName like '%

things you should do is

first, you should make a query to display all item in your product table
but in distinct order,example:

$selquery4="SELECT distinct DeptName FROM tbl_product WHERE id_company='$idcompany' group BY DeptName";
$selResult4=kal_query($selQuery4);

then for each record you can count its quantity

while ($row4=newfetch($selresult4))
{
$DeptName=$row4->DeptName;

$subQuery4="SELECT COUNT(DeptName) from tbl_product WHERE DeptName like '$Deptname';
$subResult4=kal_query($subQuery4);
$row5=newfetch($subresult4)
$count=$row5->DeptName;

}

[binto]

You have saved me many days od headache.

I used
$count = kal_num_rows($subResult4);

It is working like magic. Thanks a lot for pointing me to the right direction.