CODEWITHSUNDEEP
sqlsql-server
mac
mac

Reputation: 25

SQL Server - count how many names have 'A' and how many have 'E'

I have problem with SQL query.

I have names in column Name in Table_Name, for example: 

'Mila', 'Adrianna' 'Emma', 'Edward', 'Adam', 'Piter'

I would like to count how many names contain the letter 'A' and how many contain the letter 'E'.

The output should be:

letter_A ( 5 )| letter_E (3)

I tried to do this:

SELECT Name,
       letter_A = CHARINDEX('A', Name),
       letter_E = CHARINDEX('E', Name)
FROM   Table_Name
GROUP  BY Name
HAVING ( CHARINDEX('A', Nazwisko) != 0
          OR ( CHARINDEX('E', Nazwisko) ) != 0 )

My query only shows if 'A' or 'E' is in Name :/

Can anyone help? :)

Upvotes: 1

Views: 1006

Answers (3)

NewSQL
NewSQL

Reputation: 181

;WITH CTE
     AS (SELECT NAME
         FROM   (VALUES ('MILA'),
                        ('ADRIANNA'),
                        ('EMMA'),
                        ('EDWARD'),
                        ('ADAM'),
                        ('PITER'))V(NAME)),
     CTE_NAME
     AS (SELECT COUNT(NAME_A) NAME_A,
                COUNT(NAME_E) NAME_E
         FROM  (SELECT CASE
                         WHEN NAME LIKE '%A%' THEN NAME
                       END NAME_A,
                       CASE
                         WHEN NAME LIKE '%E%' THEN NAME
                       END NAME_E
                FROM   CTE
                GROUP  BY NAME)A)
SELECT *
FROM   CTE_NAME

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You just need to aggregate if you only need the counts.

select 
 sum(case when charindex('a',name) <> 0 then 1 else 0 end) as a_count
,sum(case when charindex('e',name) <> 0 then 1 else 0 end) as e_count
from table_name

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use conditional aggregation:

select sum(case when Nazwisko like '%A%' then 1 else 0 end) as A_cnt,
       sum(case when Nazwisko like '%E%' then 1 else 0 end) as E_cnt       
from table_name
where Nazwisko like '%A%' or Nazwisko like '%E%';

Upvotes: 1

Related Questions