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

Name
apple
banana
coconut
apricot
bear
ant

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

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


Please help

Thank You
Posted
Comments
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:
SQL
select name, (select count(*) from tablename As t2 where t2.name like LEFT(t1.name, 1) + '%') from tablename As t1
   
Comments
Sascha Lefèvre 11-May-15 15:19pm
   
+5
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