Count number of entries in a text field

[rickla]

Hi

I have stumbled upon a problem with my sql. What is need to do is to count the number of each different entry in a column called x_kontakt. The statement I tried to use was:

SELECT x_kontakt, aar, COUNT(*) AS ant
FROM statview
WHERE (aar = 2007)
GROUP BY x_kontakt, aar

This gives me an error that states that the text datatype cannot be compard unless useing IS NULL og LIKE operator.

The column x_kontakt is of type (text,null)

If I had only made it a varchar instead this would not been a problem. But that is too late now.

Hope some of you guys have a solution to my problem.

regards,
Eirik

 

[AlexCuse]

I have never used a TEXT column (they are evil!) but is aar text as well? You aren't doing any comparison on x_kontakt.

Maybe this will work:

WHERE (aar LIKE '%2007%')

In the future please post in the forum for your specific database, as they all handle things like this differently.

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[rickla]

Thanks for the reply. Maybe I didn't explain myself good enough. The field "aar" is (int,null).

What I want to do is count the number of entries example given "email", "phone" etc. that is listed in the column x_kontakt. And then run a group by x_kontakt.

The database I am using is Microsoft SQL Server 2005

Eirik

 

[PHV]

But that is too late now
Why ? You can't use an ALTER TABLE statement ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gmmastros]

>> The database I am using is Microsoft SQL Server 2005


In that case, change the data type from text to [!]varchar(max)[/!] and try again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom