Subquery (SQL error (1242): Subquery Returns more than 1 Row)

Question

I created the following query:

SELECT
    a.`CPF/CNPJ`,
    a.Cliente,
    a.Cep,
    a.Cidade,
    a.Estado,
    COUNT(a.`CPF/CNPJ`) AS Novas_Instancias,
    (
      SELECT COUNT(c.`CPF/CNPJ`)
      FROM relat_instancias c 
      GROUP BY c.`CPF/CNPJ`
    ) AS Total_Instancias
FROM relat_instancias a
LEFT JOIN relat_form_pag_cartao b
    ON a.Pedido = b.Pedido
WHERE a.Data_abertura > '2019-03-01'
    AND b.lista_casamento = 'Nao'
    AND a.Analisado IS NULL
GROUP BY a.`CPF/CNPJ`
ORDER BY Novas_Instancias DESC

That me returns:

SQL Error (1242): Subquery Returns more than 1 Row

The initial idea was to consult how many new CPF/CNPJ logs I have and this information come in a column and in another column the total with the old and new logs.

Example:

CPF XXXXXXXXXXX from January to August had 10 logs, but now it’s September and I have a new 1 that was inserted in the table.

The expected return is the next:

CPF, Date, Zip code, City, State, New log, Total log

Is there any possibility of correcting this?

Answer 1

Shouldn’t it be something like this? I am without sql to test, but I believe to be something along these lines...

SELECT
    a.`CPF/CNPJ`,
    a.Cliente,
    a.Cep,
    a.Cidade,
    a.Estado,
    SUBQUERY.Total_Instancias 
FROM relat_instancias a
INNER JOIN (
      SELECT COUNT(*) Total_Instancias ,c.`CPF/CNPJ`
      FROM relat_instancias c 
      GROUP BY c.`CPF/CNPJ`
    ) SUBQUERY ON A.CPF/CNPJ = SUBQUERY.CPF/CNPJ
LEFT JOIN relat_form_pag_cartao b
    ON a.Pedido = b.Pedido
WHERE a.Data_abertura > '2019-03-01'
    AND b.lista_casamento = 'Nao'
    AND a.Analisado IS NULL
GROUP BY a.`CPF/CNPJ`
ORDER BY Novas_Instancias DESC

Answer 2

An alternative solution would be to put a WHERE in place of GROUP BY subselect.

SELECT
    a.`CPF/CNPJ`,
    a.Cliente,
    a.Cep,
    a.Cidade,
    a.Estado,
    COUNT(a.`CPF/CNPJ`) AS Novas_Instancias,
    (
        SELECT COUNT(*)
        FROM relat_instancias c 
        WHERE c.`CPF/CNPJ` = a.`CPF/CNPJ`
    ) AS Total_Instancias
FROM relat_instancias a
LEFT JOIN relat_form_pag_cartao b
    ON a.Pedido = b.Pedido
WHERE
    a.Data_abertura > '2019-03-01'
    AND b.lista_casamento = 'Nao'
    AND a.Analisado IS NULL
GROUP BY a.`CPF/CNPJ`
ORDER BY Novas_Instancias DESC

As for the performance of this is another story.