Summary of Transaction

Table Master
-------------
BillNo   nvarchar no 100
BillDate  datetime no 8


 Data in table Master
 -----------------------
 A0300000160 2010-10-06 19:06:16.000 
 A0300000161 2010-10-06 19:06:29.000 
 A0300000162 2010-10-06 19:07:03.000 
 A0300000163 2010-10-06 19:07:34.000 
 A0300000164 2010-10-06 19:09:24.000 


Table Child
------------------

BillNo nvarchar no 40      
ProductID nvarchar no 40      
ProductRate real no 4 24   
Qty real no 4 24   
ccode nvarchar no 8      

Data in table Child
--------------------
 A0300000161 TK009 22.0 2.0 TK
 A0300000162 CC001 44.09 1.0 CC
 A0300000162 TK009 22.0 1.0 TK
 A0300000163 CP008 342.60  0.25 CP
 A0300000164 TK009 22.0 2.0 TK
 A0300000165 TK009 22.0 1.0 TK




Table Cateogry_Master
----------------------

CCode nvarchar no 40
CName nvarchar no 200
Date in Category_Master
----------------------
FC Coffee
CP Coffee Powder
SN Snacks
ES Hot Coffee
CC Cold Coffee
MR Merchandise
TK Takeaway

Expected Result

CName    Total_Bills
----------------------
Takeaway     3
Coffe Powder 1
Mixed        1     

Expected Result : Summary of transaction, if each bill has mixed cateogy of products then it will be counted as Mixed otherwise can calculate based on category code

This is my code but am not getting expecting result.

select 
  count(*) as count, 
  c.ccode 
from 
  tblbilldetails as c,
  tblbill b 
where 
  b.billno=c.billno 
  and b.billdate>='2010-10-11 11:43:48.000' 
  and b.billdate<='2010-10-11 12:43:48.000' 
group by 
  c.ccode