EN VI

Sql - how can I count the occurences of strings with letter a?

I am trying to count how many names in my table have at least one letter 'a' or 'A' and how many of them have at least one letter 'e' or 'E'

effectively my question is how to do something along these lines:

SELECT count(lower(nazwisko) like '%a%') as "Ile nazwisk z A", count(lower(nazwisko) like '%e%') as "Ile nazwisk z E"
FROM pracownicy

as this code returns

ORA-00907: missing right parenthesis

"nazwisko" is a string column. I want to do that in a single query preferably without subqueries (if possible) for learning purpouses

Im using oracle sql developer

for reference let me include the table I am working on and expected output

Solution:

Use conditional aggregation:

SELECT COUNT(CASE WHEN LOWER(nazwisko) LIKE '%a%' THEN 1 END) as "Ile nazwisk z A",
       COUNT(CASE WHEN LOWER(nazwisko) LIKE '%e%' THEN 1 END) as "Ile nazwisk z E"
FROM   pracownicy

Which, for the sample data:

CREATE TABLE pracownicy (nazwisko) AS
SELECT 'Alice' FROM DUAL UNION ALL
SELECT 'Beryl' FROM DUAL UNION ALL
SELECT 'Carol' FROM DUAL UNION ALL
SELECT 'Debra' FROM DUAL UNION ALL
SELECT 'Emily' FROM DUAL UNION ALL
SELECT 'Fiona' FROM DUAL UNION ALL
SELECT 'Glory' FROM DUAL UNION ALL
SELECT 'Heidi' FROM DUAL UNION ALL
SELECT 'Irene' FROM DUAL;

Outputs: