Text field in a group by, how can I make it work?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ThunderMusic - 27 Jan 2006 15:14 GMT

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

Reply to this Message

John Kane - 29 Jan 2006 17:20 GMT

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

Signature

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

> Hi,
> I need to do a sum this way : Select Field1, Sum(Field2) as sumField2 From
[quoted text clipped - 9 lines]
>
> btw, I'm in SQL Server 2000

Reply to this Message