Query to list number of occurrences

Asked

Viewed 1,349 times

0

I am trying to list how many CATEGORIES each PERSON has but I only want to return if PERSON has more than one CATEGORY. My query is like this and does not return anything. And I know there are cases to show.

SELECT COUNT(*), PESSOA_FK,CATEGORIA_IDENTIFICACAO_FK FROM IDENTIFICACAO_PESSOA
GROUP BY PESSOA_FK,CATEGORIA_IDENTIFICACAO_FK
HAVING COUNT(*) > 1;
sql

  • There’s some prank going on that I’m having trouble identifying. If I use only PESSOA_FK it brings many records but I need to know the amount of CATEGORY each person has.

2 answers

1


The way it is query will only return if the person has more than once the same category. To list the number of categories per person, you need to not group by category:

SELECT COUNT(*), PESSOA_FK 
FROM IDENTIFICACAO_PESSOA 
WHERE PESSOA_FK IN (SELECT PESSOA_FK FROM IDENTIFICACAO_PESSOA WHERE CATEGORIA_IDENTIFICACAO_FK = 10) --ou qualquer outra validação
GROUP BY PESSOA_FK 
HAVING COUNT() > 1

Obs: this query is considering that the category is mandatory; thus, each occurrence in the table IDENTIFICACAO_PESSOA shall have one person and one category.

  • Can you tell me why when I specify CATEGORY in Where it brings nothing ? So yours really worked.

  • when you group (group by) for more than one field, he will count only if all are equal; that is, if the person joão has two categories, alto and novo, your query would return total 1 to "high john" and total 1 to "new john," which is not what you want. Grouping only by name, he will bring total 2 for "joão" (1 for "alto" and 1 for "novo")

  • I did, it was a blind knot of mine. I’ll validate your answer but just edit it to look like this: SELECT COUNT(), PESSOA_FK FROM IDENTIFICACAO_PESSOA WHERE PESSOA_FK IN (SELECT PESSOA_FK FROM IDENTIFICACAO_PESSOOA WHERE CATEGORIA_IDENTIFICACAO_FK = 10) GROUP BY PESOA_FK HAVING COUNT COUNT() > 1

  • I can even edit but I don’t know if the change is related to the question; if I understand it is a condition that you until then did not need :P

  • I may have expressed myself wrong in the question or it was not clear rsrs. But this is my goal :) Anyway thank you very much.

1

Assuming a structure similar to yours:

CREATE TABLE IDENTIFICACAO_PESSOA
(
    PESSOA_FK BIGINT,
    CATEGORIA_IDENTIFICACAO_FK character varying(1)
);

With the following test data:

INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 1 , 'B' );
INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 2 , 'A' );
INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 2 , 'B' );
INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 2 , 'C' );
INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 3 , 'X' );
INSERT INTO IDENTIFICACAO_PESSOA ( PESSOA_FK, CATEGORIA_IDENTIFICACAO_FK ) VALUES ( 4 , 'Y' );

Your query would look like this:

SELECT
    DISTINCT
        PESSOA_FK AS id_pessoa,
        COUNT(1) AS qtd_categorias
FROM
    IDENTIFICACAO_PESSOA
GROUP BY
    PESSOA_FK
HAVING
    COUNT(1) > 1;

SQL Fiddle: http://sqlfiddle.com/#! 17/22a21/1/0

Browser other questions tagged sql

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