sql server full text search: Hi,
I need to do a sum this way : Select Field1, Sum(Field2) as sumField2 From
Table1 Group By Field1. I think the syntax is correct, but the problem is
that Field1 is of type Text (and not varchar). So I receive an error telling
me I can't "group by" with a field of type "Text". Is there a way I can
convert this field into something else directly in the query so the query
works?

Thanks

ThunderMusic

btw, I'm in SQL Server 2000

ThunderMusic,
Here are a couple of examples using SQL 2000 Profiler trace data imported
into a SQL table. The column textdata is defined as TEXT:


-- Get total CPU used by each distinct query across multiple executions.
select count (*) as cnt, sum (cpu) as tot_cpu, substring (textdata, 1, 14)
as qry
from trace
where EventClass in (12, 10)
group by substring (textdata, 1, 14)
order by sum (cpu) desc

/** Determine which SPs have the most reads **/
SELECT 'Stored Proc (StoreProduct)' = CONVERT(varchar(30),
SUBSTRING(textdata, 16, (PATINDEX('% %', textdata)-16))), 'Avg. Logical
Reads' = AVG(Reads)
FROM tblShopTrace t, tblEventClass e
WHERE t.EventClass = e.EventClassID
AND e.EventName = 'RPC:Completed'
AND t.textdata not like 'declare%'
GROUP BY CONVERT(varchar(30), SUBSTRING(textdata, 16, (PATINDEX('% %',
textdata)-16)))
ORDER BY AVG(Reads) DESC


hope this helps,
John

--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/




[quoted text, click to view]