By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

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

P: 4
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);
Feb 5 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 2,878
Try changing the data type of Transactiontype column.

-- CK
Feb 5 '09 #2

P: 4
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
Feb 5 '09 #3

Expert 2.5K+
P: 2,878
Try converting it to NVARCHAR.

-- CK
Feb 5 '09 #4

P: 4
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?
Feb 6 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.