CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Christian
Christian

Reputation: 1853

Counting the number of rows based on like values

I'm a little bit lost on this. I would like to list the number of names beginning with the same letter, and find the total amount of names containing that first same letter.

For instance:

name   |  total
-------|--------
 A     |   12
 B     |   10
 C     |   8
 D     |   7
 E     |   3
 F     |   2
   ...
 Z     |   1

12 names beginning with letter 'A', 10 with 'B' and so on.

This is what I have so far

SELECT 
LEFT(customers.name,1) AS 'name'
FROM customers
WHERE
customers.name LIKE '[a-z]%'
GROUP BY name

However, I'm unsure how I would add up columns based on like values.

Upvotes: 0

Views: 55

Answers (2)

VBlades
VBlades

Reputation: 2251

This should work for you:

SELECT 
    LEFT(customers.name,1) AS 'name',
    COUNT(*) AS NumberOfCustomers
FROM customers
WHERE
    customers.name LIKE '[a-z]%'
GROUP BY LEFT(customers.name,1)

EDIT: Forgot the explanation; as many have mentioned already, you need to group on the calculation itself and not the alias you give it, as the GROUP BY operation actually happens prior to the SELECT and therefore has no idea of the alias yet. The COUNT part you would have figured out easily. Hope that helps.

Upvotes: 3

Olaf Dietsche
Olaf Dietsche

Reputation: 74048

You don't want to count the names, but only the first letters. So you must not group by name, but group by the first letter 

SELECT LEFT(name, 1) AS name, count(*)
FROM customers
GROUP BY LEFT(name, 1)

SQLFiddle

Upvotes: 2

Related Questions