Difficulty to Mount SQL Query with UNION with SUM

Navigation
1

I have 2 tables that stores different sales type, and I need to add everything to get the value to generate the collection, but I'm not able to mount the sql query, as 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 at or near "group" LINE 30: group by CL.id_cadastro_cliente

    
asked by anonymous 14.12.2015 / 20:19

2 answers

0

SELECT CONSULTA.NAME, CONSULTA.ID_CLIENTE, sum (CONSULTA.valor_total) as total_value

from (

(SELECT CL1.NOME AS NAME, CL1.id_cadastro_cliente AS CLIENT_ID, sum (L1.  FROM CADASTRO_ATENDIMENTO AS C1  INNER JOIN CADASTRO_ATENDIMENTO_ITEM AS L1 ON (C1.id_cadastro_atendimento = L1.id_cadastro_atendimento)  INNER JOIN CADASTRO_Client AS CL1 ON (CL1.id_cadastro_cliente = C1.id_cadastro_cliente)  Where C1.TIPO_OPERACAO = 'A VIEW' AND L1.FATURA_NUMERO = 0 AND L1.FATURA_ANO = 0 AND L1.FATURA_PAGA = 'FALSE' AND C1.REMETENTE LIKE '%'  AND C1.DATA_ATENDIMENTO BETWEEN '2014-01-01' and '2016-01-01'  group by CL1.id_cadastro_cliente  ORDER BY CL1.NOME)

union all

(SELECT CL2.NOME AS NAME, CL2.id_cadastro_client AS CUSTOMER_ID, sum (end_ counter-initial_ counter) as TOTAL_VALUE  FROM cadastro_maquina_franquia_asendimento AS M2  INNER JOIN CADASTRO_Client AS CL2 ON (CL2.id_cadastro_cliente = M2.id_cliente)  WHERE M2.CARTAO IS NULL AND M2.ID_CLIENTE > 0  group by CL2.id_cadastro_client)

) the CONSULTATION group by CONSULTA.ID_CLIENTE, CONSULTA.NOME HAVING SUM (CONSULTA.value_total) > 200 ORDER BY CONSULTA.ID_CLIENTE

The error actually was that in the end it should be query.field

    
15.12.2015 / 12:57
1

INNER JOIN ON before group by 

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

In addition ... every time you use a table inside the command, you must name it in a different way ...

In the first select you do INNER JOIN with CADASTRO_Cliente as CL .. in the end you make another INNER JOIN CADASTRO_Cliente as CL ... should be CL2 for example ...

    
14.12.2015 / 20:23
Number of rows changed by the user Code that leverages decimal value in C # for Java / Android