Why SQL can't group by Text???

[georgepatotk]

I have such fields in a Table

 

StockNumber = int [primary]

BranchCode = int [foreign]

InvoiceNumber = Text [Normal data]

 

I do as below but why why why???

 

"SELECT StockNumber, BranchNumber, COUNT(InvoiceNumber) FROM tblStock GROUP BY InvoiceNumber, StockNumber, BranchCode"

 

How to make Text Group-able?? Please help me..

[PlausiblyDamp]

I have such fields in a Table

 

StockNumber = int [primary]

BranchCode = int [foreign]

InvoiceNumber = Text [Normal data]

 

I do as below but why why why???

 

"SELECT StockNumber, BranchNumber, COUNT(InvoiceNumber) FROM tblStock GROUP BY InvoiceNumber, StockNumber, BranchCode"

 

How to make Text Group-able?? Please help me..

How large an InvoiceNumber do you use? a char/varchar can hold approx. 8K of text while the unicode variants (nchar/nvarchar) can hold approx 4K of text - are these not suitable.

Text as a datatype is really a large binary field and can store up to 2G of data per field - it's this large storage capacity that prevents it's use in certain situations i.e. grouping, indexing etc.

 

If I was you I'd rethink the choice of data type and also investigate to see if it has been used elsewhere in the data base. There are many reasons to avoid Text as a data type for smaller string based dataitems (indexing, potential performance issues, recoverability, issues with replication etc.)

[georgepatotk]

for char = 4k of text.

means how many characters? mind to tell??

[PlausiblyDamp]

1 byte per character for char/varchar, 2bytes per character for nchar/nvarchar.

a char/varchar field can hold approx 8,000 characters while a nchar/nvarchar can hold approx. 4,000 characters.

[georgepatotk]

1 byte per character for char/varchar, 2bytes per character for nchar/nvarchar.

a char/varchar field can hold approx 8,000 characters while a nchar/nvarchar can hold approx. 4,000 characters.

 

Thanks.

I think I got to change my mind in choosing char instead of Text.

SO, Char can be grouped in SQL?

[PlausiblyDamp]

Thanks.

I think I got to change my mind in choosing char instead of Text.

SO, Char can be grouped in SQL?

Certainly - it was why I suggested the change:D

[georgepatotk]

Thanks a lot.

kiss kiss....

[Nerseus]

Why do you even need InvoiceNumber in that query? It looks like you're grouping by only two fields and just getting a count? Even if you wanted to ues "... COUNT(DISTINCT InvoiceNumber)", you still wouldn't need the InvoiceNumber in the GROUP BY.

 

-nerseus

[georgepatotk]

In my case, I want to show to the users the details in a datagrid.

 

Databases Fiels:

[tblStock_BRanch] : Stock_BranchNumber, StockNumber, BranchNumber, InvoiceNumber, and PurchaseDate

 

[tblStock] : StockNumber, StockCode, StockCost

 

[tblBranch] : BranchNumber, BranchCode, BranchDescription

 

what i am doing here is:

 

SELECT sb.StockNumber, COUNT(StockNumber) AS Unit, s.StockCode, sb.BranchNumber, s.BranchCode, sb.InvoiceNumber, sb.PurchasesDate

 

FROM tblStock_Branch sb, tblStock s, tblBranch b

 

WHERE s.StockNumber = sb.StockNumber AND b.BranchNumber = sb.BranchNumber

 

GROUP BY sb.StockNumber, s.StockCode, sb.BranchNumber, s.BranchCode, sb.InvoiceNumber, sb.PurchasesDate

 

so the output will be:

 

StockCode BranchCode Unit InvoiceNumber PurchasesDate

MILO/001 HQ 3 MMC1354 02/03/2004

MILO/001 HQ 6 MMC1360 06/03/2004

Ovatine/002 HQ 10 MMC1450 07/03/2004