Use of Count() for data recurrences

Asked

Viewed 35 times

0

I have the following appointment:

SELECT
     "metas"."Segmento",
     "metas"."Perfil",
     "metas"."Meta",
     "Negócios"."Nome"
FROM  "metas"
JOIN "Negócios" ON "Negócios"."Perfil"  = "metas"."Perfil"
    AND "Negócios"."Segmento" = "metas"."Segmento"
JOIN "cliente potencial" ON "cliente potencial"."idNegocio" = "Negócios"."Id"  
WHERE "metas"."Mês referência" = 'Novembro 2020'
AND MONTH("cliente potencial"."data") = 11

She brings me the following return:

segmento    perfil    meta    nome
seg.a       x         20      joao
seg.a       x         20      pedro
seg.a       x         20      roberto
seg.b       y         30      felipe
seg.b       y         30      marcela

How do I make a count to return instead of the name to amount of business? The expected return would be like this:

segmento    perfil    meta    qtd
seg.a       x         20      3
seg.b       y         30      2

I tried to make a count in name but only returns 1...

sql

1 answer

1


Just use the GROUP BY clause with the COUNT aggregation function.

SELECT
     "metas"."Segmento",
     "metas"."Perfil",
     "metas"."Meta",
     COUNT(*) AS qtd
FROM  "metas"
JOIN "Negócios" ON "Negócios"."Perfil"  = "metas"."Perfil"
    AND "Negócios"."Segmento" = "metas"."Segmento"
JOIN "cliente potencial" ON "cliente potencial"."idNegocio" = "Negócios"."Id"  
WHERE "metas"."Mês referência" = 'Novembro 2020'
AND MONTH("cliente potencial"."data") = 11
GROUP BY "metas"."Segmento", "metas"."Perfil", "metas"."Meta"

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.