Make a SQL language design that returns the number of clients who have no business done

Question

I can not solve this issue, I want it to show number of customers without repeating primary key, no realized deals and show a warning if customers have done business. There was this mistake here.

---

CREATE FUNCTION sem_negocios() RETURNS integer AS 
$$
    SELECT count(*), CASE WHEN identidade NOT IN (SELECT DISTINCT identidade FROM negocio)
        THEN 'negociado'
        ELSE 'nao negociado'
        END AS identidade
    FROM cliente
    GROUP BY CASE 
        WHEN identidade IN (SELECT DISTINCT identidade FROM negocio)
        THEN 'negociado'
        ELSE 'nao negociado'
        END
    ORDER BY identidade;

$$
LANGUAGE 'SQL';

---

ERROR: column "client.identity" should appear in the GROUP BY clause or be used in an aggregation function LINE 3: SELECT Count(*), CASE WHEN identity NOT IN (SELECT DISTIN... ^

********** Error **********

ERROR: "client.identity" column should appear in the GROUP BY clause or be used in an aggregation function SQL state: 42803 Character: 83

Answer 1

If your function should only return an integer, you cannot have other columns, only Count(*). Apart from this case stack when it’s a mess.

The function code would look like this:d

CREATE FUNCTION sem_negocios() RETURNS integer AS 
$$
    SELECT 
        count(*)
    FROM cliente
    where cliente.id not in (select distinct negocio.idcliente from negocio);
$$
LANGUAGE 'SQL';

Answer 2

Assuming your model has a similar structure:

CREATE TABLE cliente
(
    cod INTEGER,
    identidade INTEGER
);

CREATE TABLE negocio
(
    cod INTEGER,
    identidade INTEGER
);

With this data:

INSERT INTO cliente( cod, identidade ) VALUES ( 1, 1000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 2, 2000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 3, NULL );
INSERT INTO cliente( cod, identidade ) VALUES ( 4, 4000 );
INSERT INTO cliente( cod, identidade ) VALUES ( 5, NULL );

INSERT INTO negocio( cod, identidade ) VALUES ( 1, 1000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 2, 2000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 3, 4000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 4, 1000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 5, 2000 );
INSERT INTO negocio( cod, identidade ) VALUES ( 6, 4000 );

Your job would look like this:

CREATE OR REPLACE FUNCTION clientes_sem_negocio()
RETURNS INTEGER AS 
$body$
BEGIN
    RETURN(
        SELECT
            DISTINCT count(1)
        FROM
            cliente c
        LEFT JOIN
            negocio n ON ( n.identidade = c.identidade )
        WHERE
            n.identidade IS NULL
    );
END
$body$
LANGUAGE plpgsql;

Testing:

SELECT clientes_sem_negocio() AS qtd;

Exit: