Need Help - Multiple Column in Group By clause with Data Type Error

[Russel0725]

This is the table structure I'm trying to query:

Storename varchar 50
Bussinessdate datetime 8
txnnumber text 16
subtotal float 8
discounts float 8
paidamount float 8
transactiontype text 16
time datetime 8

This is my query which results to " The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

select Storename, sum(Subtotal - Discounts) as gross,
Transactiontype, datepart(weekday, Time) as DWeekday,
datepart(hour, Time) as DHour, count(txnnumber) as tcount
from tMas_TxnSummary
where Bussinessdate >= '01/01/2008' and Bussinessdate <= '01/31/2008'
and Storename = 'Sample'
group by Storename,Transactiontype, datepart(hour, Time),datepart(weekday, Time);

[ck9663]

Try changing the data type of Transactiontype column.

-- CK

[Russel0725]

I'm sorry I can't do that. It is an established database already and I'm not allowed to change anything, only to query. How else can I accomplish a result set like this?

BussinessDate Storename Transaction Type Dayoftheweek Hour Tcount Gross

[ck9663]

Try converting it to NVARCHAR.

-- CK

[Russel0725]

Thanks a lot it worked when i converted transactiontype to NVARCHAR. I tried also to do the same for the txnnumber field but COUNT applied to that converted field gives me an error, or does not work. What is the correct syntax to perform COUNT on a converted field?