Click here to Skip to main content
14,978,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Everyone

I am working on a project where I have to get the count of the columns beginning with the alphabet in a table.

For example
Below the table


The result should be

Name count
apple 3
banana 2
coconut 1
apricot 3
bear 2
ant 3

I tried the following query but I am getting the total count of the records for all rows

select name,(select count(*) from tablename where name like LEFT(name,1)+ '%') from tablename

Please help

Thank You
virusstorm 11-May-15 14:59pm
I'm having a hard time following what you are counting. For the example "apple", what does the value "3" represent?

1 solution

The problem is in your sub-query - it doesn't know which name column you're referring to.

You've essentially asked "how many names start with their own first letter?", to which the answer is "all of them".

You need to add an alias to the tables, so that you can refer to the column from the correct table:
select name, (select count(*) from tablename As t2 where like LEFT(, 1) + '%') from tablename As t1
Sascha Lefèvre 11-May-15 15:19pm
JPais 12-May-15 0:48am
Thank you so much.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900