Difficulty Mounting SQL Query with UNION with SUM

Question

I have 2 tables that stores type of different sales, and I need to add everything to get the amount to generate the charge, however I am not able to mount the sql query, follows:

SELECT CL.NOME,CL.id_cadastro_cliente,  sum (L.VALOR_LANCAMENTO) as valor_total

 from (

(SELECT CL.NOME,CL.id_cadastro_cliente,  sum (L.VALOR_LANCAMENTO) as valor_total
 FROM CADASTRO_ATENDIMENTO AS C
 INNER JOIN CADASTRO_ATENDIMENTO_ITEM AS L ON (C.id_cadastro_atendimento = L.id_cadastro_atendimento)
 INNER JOIN CADASTRO_Cliente AS CL ON (CL.id_cadastro_cliente = C.id_cadastro_cliente)
 Where C.TIPO_OPERACAO = 'A VISTA' AND L.FATURA_NUMERO = 0 AND L.FATURA_ANO = 0 AND L.FATURA_PAGA = 'FALSE' AND C.REMETENTE LIKE '%'
 AND C.DATA_ATENDIMENTO BETWEEN '2014-01-01' and '2016-01-01'
 group by CL.id_cadastro_cliente
 HAVING SUM(L.VALOR_LANCAMENTO) > 100
 ORDER BY CL.NOME)

union all

(SELECT CL.nOME, CL.id_cadastro_cliente, sum (contador_final - contador_inicial) as valor_total
 FROM cadastro_maquina_franquia_atendimento AS M
 INNER JOIN CADASTRO_Cliente AS CL ON (CL.id_cadastro_cliente = M.id_cliente)
 WHERE CARTAO IS NULL AND ID_CLIENTE > 0
 group by CL.id_cadastro_cliente)


) as CONSULTA


INNER JOIN CADASTRO_Cliente AS CL 
group by CL.id_cadastro_cliente

I’m having the following error:

ERROR: syntax error in or next to "group" LINE 30: group by CL.id_customer registration

Answer 1

INNER JOIN ON before group by

INNER JOIN CADASTRO_Cliente as CL ON CL.chave = ?.chave

Also... each time you use a table inside the command, Voce should name it differently...

In the first select Voce does INNER JOIN with CADASTRO_Cliente as CL.. at the end Voce makes another INNER JOIN CADASTRO_Cliente as CL... should be CL2 for example...

Answer 2

SELECT QUERY.NAME, QUERY.ID_CLIENTE, sum (QUERY.value_total) the total value_total

from (

(SELECT CL1.NAME AS NAME,CL1.id_registration AS ID_CLIENTE, sum (L1.VALOR_LANCAMENTO) as VALOR_TOTAL FROM CADASTRO_ATENDIMENTO AS C1 INNER JOIN CADASTRO_ATENDIMENTO_ITEM AS L1 ON (C1.id_registration = L1.id_registration) INNER JOIN Customer Registration AS CL1 ON (CL1.id_customer registration = C1.id_customer registration) Where C1.TIPO_OPERACAO = 'A VISTA' AND L1.FATURA_NUMERO = 0 AND L1.FATURA_ANO = 0 AND L1.FATURA_PAGA = 'FALSE' AND C1.SENDER LIKE '%' AND C1.DATA_ATENDIMENTO BETWEEN '2014-01-01' and '2016-01-01' group by CL1.id_sign up ORDER BY CL1.NAME)

Union all

(SELECT CL2.name AS NAME, CL2.id_registration AS ID_CLIENTE, sum (final count - initial count) as VALOR_TOTAL FROM cadastro_maquina_franquia_atendimento AS M2 INNER JOIN Customer Registration AS CL2 ON (CL2.id_customer registration = M2.id_client) WHERE M2.CARTAO IS NULL AND M2.ID_CLIENTE > 0 group by CL2.id_sign up)

) THE CONSULTATION group by CONSULTA.ID_CLIENTE, QUERY.NAME HAVING SUM(QUERY.value_total) > 200 ORDER BY QUERY.ID_CLIENTE

the mistake was that in the end it should be consultation.