I'm trying to list the amount of CATEGORIES that each PERSON has but only want to return if the PERSON has more than one CATEGORY. My query is like this and returns nothing. 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;
As the query is only returned if the person has more than once the same category. To list the number of categories per person, you do not need to 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 consultation is considering that the category is mandatory; therefore, each occurrence in the IDENTIFICACAO_PESSOA table will have one person and one category.
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 looks 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: link