Do an SQL sub-query

Question

I am trying to make several queries (counting the amount of certain elements) within the same table column, and send each query to a different column. Ex: I have column of names, and I want to consult the quantity of some surnames as: quantity of "Silva", "Alves" and "Santos". And create a "Silva" column with the number of names with Silva, and etc.

What I thought:

SELECT COUNT(`coluna_1`) AS `Nome`

(SELECT COUNT (`coluna_1`) AS `Nome_1`
FROM `Tabela_1` AS `A`
WHERE (`A`.`coluna_1` LIKE '%nome_1%')

FROM `Tabela_1` AS `B`
WHERE (`B`.`coluna_1` LIKE '%nome%'

Answer 1

In some Dbms it is possible to perform a filter when using an aggregation function, such as Count. This avoids several nested queries.

In Postgres, for example, your query could be written as follows:

SELECT
       count(nome) FILTER (WHERE nome like '%Silva%') as Silva,
       count(nome) FILTER (WHERE nome like '%Alves%') as Alves,
       count(nome) FILTER (WHERE nome like '%Santos%') as Santos
FROM tabela

In other Dbms it is possible to use a CASE WHEN clause as follows:

SELECT
       count(CASE WHEN nome like '%Silva%' THEN 1 END) as Silva,
       count(CASE WHEN nome like '%Alves%' THEN 1 END) as Alves,
       count(CASE WHEN nome like '%Santos%' THEN 1 END) as Santos
FROM tabela

Here an executable example of the codes described.

Answer 2

Hey, how you doing? What you are looking for can be easily achieved with the following consultation (very similar to what you had even thought):

select (select count(nome) from Pessoas where nome like '%Silva%') as Silva,
       (select count(nome) from Pessoas where nome like '%Alvez%') as Alvez,
       (select count(nome) from Pessoas where nome like '%Santos%') as Santos

I hope it helped ;)