How to use COUNT with two columns?

Question

Post reply

How to use COUNT with two columns?

SqlNewbieAT

Grasshopper

Points: 15
More actions
April 3, 2010 at 7:52 am

#213360

Help! I need to provide a list with counted items per server. My table has two character fields:
Column1 | Column2
Server1 | ValueA
Server1 | ValueB
Server2 | ValueA
Server2 | ValueC
Server1 | ValueA
Server2 | ValueC
The result should look like
Server1 | ValueA | 2
Server1 | ValueB | 1
Server2 | ValueA | 1
Server2 | ValueC | 2
How to achieve that??? I have to realise this with MSQL 2000 and it should be in a view.
Many, many thanks for your help!!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

A. Harmon SSC Eights! Points: 995 More actions · Answer 1

Hey there,

you should do a group by with both your columns and a count of just the second column. So...

SELECT Column1, Column2, COUNT(Column2) as 'Count'

FROM TableName

GROUP BY Column1, Column2

-Andrew

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

you might want an ORDER BY at the end as well.

Grouping by multiple columns allows you to count unique values.

SqlNewbieAT Grasshopper Points: 15 More actions · Answer 3

Many thanks for the quick reply. This one I have tried already, but it returns always with this error message:

Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

How can I make it working??

A. Harmon SSC Eights! Points: 995 More actions · Answer 4

ok, this is a data type issue. Can you provide the data types of your columns?

SqlNewbieAT Grasshopper Points: 15 More actions · Answer 5

Column1 is nvarchar(256), Not Null

Column2 is ntext, Not Null

Btw, the two columns are in a view. (two related tables behind it).

A. Harmon SSC Eights! Points: 995 More actions · Answer 6

any reason you are using ntext for column2? Unless you have a good business reason, I'd try switching it to varchar(255)... the 255 represents the max length of the items in the column. If they are going to be more that 255, you can use a larger number.

SqlNewbieAT Grasshopper Points: 15 More actions · Answer 7

Hm, there is no way to change the original database. The tables and fields are from a Blackberry SQL database (!).

The only thing I can do is to create a custom view and store it in the DB. This is what I have done. My view contains the two columns in question. (they are coming from two complex source tables).

Is there a way to work around the data type issue?

A. Harmon SSC Eights! Points: 995 More actions · Answer 8

hmm, you could try converting that field in the query

SELECT Column1, CONVERT(varchar(max), Column2), COUNT(Column2) as 'Count'

FROM TableName

GROUP BY Column1, Column2

not sure if you'd need to do that convert inside the count function or in the group by statement as well.

you'll have to test a bit, but see if that does it for you.

-Andrew

A. Harmon SSC Eights! Points: 995 More actions · Answer 9

actually, you said this was SQL 2000. Not sure if you can do varchar(max)... never worked with 2000. If you get an error converting, try something like varchar(8000) instead

SqlNewbieAT Grasshopper Points: 15 More actions · Answer 10

Cool, now it works!! Many thanks for your quick and good help 🙂

SELECT [MachineName], convert(varchar(255),[ServerDN]), count(convert(varchar(255),[serverdn])) as 'Count' FROM [BESMgmt].[dbo].[MyView]

group by [machinename], convert(varchar(255),[serverdn])

I had do convert it everywhere.

Excellent! 😀

A. Harmon SSC Eights! Points: 995 More actions · Answer 11

Great, make sure that none of your values in column2 are longer than 255 characters or they may be truncated. Glad I could help.

-Andrew

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 12

The CONVERT or a CAST as shown above is what you need in the query to change the data types so that you can group them.