Select Distinct

Question

Post reply

Select Distinct

er sql

SSC Eights!

Points: 943
More actions
May 4, 2005 at 4:08 pm

#163390

Hi, I need to count distinct records in a table. Actual data looks like this.
D0004A
D0004B
D0005C
D0008B
D0005A
I need to count how many D0004 exist in the table, It doesn't matter if it has A,B,C or any onther character.
Thanks for your help

Viewing 7 posts - 1 through 6 (of 6 total)

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

David.Poole SSC Guru Points: 75656 More actions · Answer 1

SELECT LEFT(YourColumn,5) AS Code, Count(*) AS Records

FROM dbo.YourTable

GROUP BY LEFT(YourColumn,5)

LinkedIn Profile

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 2

If you wanted to completely eliminate alpha characters, including the leading one, you could use the SUBSTRING function:

SELECT substring(account_k, 2, 4) AS Code, Count(*) AS Records

FROM dbo.YourTable

[WHERE substring(account_k, 2, 4) = '0004']

GROUP BY SUBSTRING(YourColumn,2,4)

HTH

Don

noeld SSC Guru Points: 96590 More actions · Answer 3

??

Select Count(*) from Yourtable where YourColumn like 'D0004%'

* Noel

er sql SSC Eights! Points: 943 More actions · Answer 4

the problem is when the character is not allways in the same position, it could be D00A, OR D000000A, the data stored at the column has different lenght.

Thank for the help and comments.

noeld SSC Guru Points: 96590 More actions · Answer 5

I don't really understand how you want to group them but:

Ist this what you are after ?

SELECT LEFT(YourColumn,Len(YourColumn)-1) AS Code, Count(*) AS Records

FROM dbo.YourTable

GROUP BY LEFT(YourColumn,Len(YourColumn)-1)

[Edit:] Credit To David Post

* Noel

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

(weird, the system just deleted PART of my post!)

If your data is not consistent, it's either going to have to be cleaned up so that it is or you're going to have to live with problems. Data being different lengths is not always a problem as long as the left-aligned significant parts are consistent.

Assuming the data element's definition is something like this: "Element starts with a D, contains one or more zeroes, contains a single numeric digit, followed by one letter" then it can be cleaned up through string manipulation possibly with pattern matching.

But not knowing what rules build that value, it's hard to offer a good solution.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]