how to count a data.

Question

how to count a data.

immaduddinahmed

Ten Centuries

Points: 1375
More actions
December 29, 2014 at 1:22 am

#148779

hi,
my data is like this.
---------code----------------name
ACT706022774CCI0---------CCI
ACT706022774CCI0---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI
i want a query that show data like this.
---------code--------------------name
---------ACT ----------------------2
---------CDF-----------------------3
thanks for the help.
immad

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

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

So create a column in your base query that grabs the first three characters of the string, and then base your summary query on it.

Shafat Husain SSCrazy Points: 2754 More actions · Answer 2

Dear Immaduddin,

Use the Below mentioned query

Select Distinct(LEFT(Code,3)) as CODE,COUNT(Name) as Name

From [TABLENAME]

Group by LEFT(Code,3)

Hope it Helps..!!

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Shafat Husain (12/29/2014)
Dear Immaduddin,
Use the Below mentioned query
Select Distinct(LEFT(Code,3)) as CODE,COUNT(Name) as Name
From [TABLENAME]
Group by LEFT(Code,3)

Why the distinct when you have a group by, and why are you wrapping superfluous brackets around the LEFT? It makes it look like DISTINCT is a function, which I'm sure you're aware it's not.

With the unnecessary distinct and brackets taken out:

Select LEFT(Code,3) as CODE, COUNT(*) as Name

From [TABLENAME]

Group by LEFT(Code,3)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shafat Husain SSCrazy Points: 2754 More actions · Answer 4

Dear Gila,

I started the query with

Select Distinct(Left(Code,3)..... to check for the first three characters.

and then added the rest of the code.

Forgot to remove the Distinct. 😛

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I still don't understand why you're putting brackets around DISTINCT as if it was a function. All that's going to do is confuse people who read the code.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shafat Husain SSCrazy Points: 2754 More actions · Answer 6

Old bad habits die hard...!! :blush:

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)