How to join four tables and use SUM and group by-CodePudding

I have a Firebird database and I have a query that joins info from four tables, but I also need to SUM and group By

My query is this:

SELECT ARQOS.CNPO AS CNPOARQOS, 
       ARQOS.CODSEQ AS CODSEQARQOS, 
       ARQOS.CCLIENTE, 
       ARQOS.CREF, 
       ARQOS.CQUANT, 
       ARQOS.CCHAR5, 
       ARQOS.EMAIL, 
       ARQOS.ORCAGERA, 
       PRIDAT.CODSEQ as CODSEQPRIDAT,
       PRIDAT.CCHAR1, 
       PRIDAT.QUANTIDADE, 
       PRIDAT.CLIENTE, 
       PRIDAT.PRECOUNITARIO, 
       PRIDAT.PRECOFINAL, 
       PRIDAT.PRECOPORMIL, 
       REPLACE(CODITEM,'.','') AS CODITEM, 
       MATEMPEN.NOMEITEM, 
       CLIENTES.CRAZAO, 
       REPLACE(REPLACE(REPLACE(CCGC,'-',''), '/', ''),'.','') AS CCGC, 
       CLIENTES.CINSCEST, 
       CLIENTES.CINSCMUN
from ARQOS
JOIN PRIDAT ON ARQOS.CODSEQ = PRIDAT.CODSEQ
JOIN MATEMPEN SELECT NOMEITEM, SUM (QUANTORIG) AS QTDTOTAL
              FROM MATEMPEN
              GROUP BY NOMEITEM ON MATEMPEN.CODEMPENHO=ARQOS.CNPO
JOIN CLIENTES ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
WHERE ARQOS.CNPO=32838

CodePudding user response：

Change the query to:

SELECT 
        ARQOS.CNPO AS CNPOARQOS, 
        ARQOS.CODSEQ AS CODSEQARQOS, ARQOS.CCLIENTE, ARQOS.CREF, 
        ARQOS.CQUANT, 
        ARQOS.CCHAR5, 
        ARQOS.EMAIL, 
        ARQOS.ORCAGERA, 
        PRIDAT.CODSEQ as CODSEQPRIDAT,
        PRIDAT.CCHAR1, 
        PRIDAT.QUANTIDADE, 
        PRIDAT.CLIENTE, PRIDAT.PRECOUNITARIO, 
        PRIDAT.PRECOFINAL, 
        PRIDAT.PRECOPORMIL, 
        REPLACE(CODITEM,'.','') AS CODITEM, 
        MATEMPEN.NOMEITEM, 
        CLIENTES.CRAZAO, 
        REPLACE(REPLACE(REPLACE(CCGC,'-',''), '/', ''),'.','') AS CCGC, 
        CLIENTES.CINSCEST, 
        CLIENTES.CINSCMUN
from ARQOS
JOIN PRIDAT ON ARQOS.CODSEQ = PRIDAT.CODSEQ
JOIN MATEMPEN ON MATEMPEN.CODEMPENHO=ARQOS.CNPO
JOIN CLIENTES ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
JOIN 
       (  SELECT NOMEITEM, 
                 SUM (QUANTORIG) AS QTDTOTAL
          FROM MATEMPEN
          GROUP BY NOMEITEM 
        ) as t1 ON t1.column = table.column ---here add the join condition (the t1 columns are NOMEITEM or QTDTOTAL)
        
WHERE ARQOS.CNPO=32838

CodePudding user response：

I changed to this:

SELECT 
        ARQOS.CNPO AS CNPOARQOS, 
        ARQOS.CODSEQ AS CODSEQARQOS, ARQOS.CCLIENTE, ARQOS.CREF, 
        ARQOS.CQUANT, 
        ARQOS.CCHAR5, 
        ARQOS.EMAIL, 
        ARQOS.ORCAGERA, 
        PRIDAT.CODSEQ as CODSEQPRIDAT,
        PRIDAT.CCHAR1, 
        PRIDAT.QUANTIDADE, 
        PRIDAT.CLIENTE, PRIDAT.PRECOUNITARIO, 
        PRIDAT.PRECOFINAL, 
        PRIDAT.PRECOPORMIL, 
        REPLACE(CODITEM,'.','') AS CODITEM, 
        MATEMPEN.NOMEITEM, 
        CLIENTES.CRAZAO, 
        REPLACE(REPLACE(REPLACE(CCGC,'-',''), '/', ''),'.','') AS CCGC, 
        CLIENTES.CINSCEST, 
        CLIENTES.CINSCMUN
from ARQOS
JOIN PRIDAT
ON ARQOS.CODSEQ = PRIDAT.CODSEQ
JOIN MATEMPEN ON MATEMPEN.CODEMPENHO=ARQOS.CNPO
JOIN (SELECT NOMEITEM, SUM (QUANTORIG) AS QTDTOTAL
FROM MATEMPEN
GROUP BY NOMEITEM)
JOIN CLIENTES
ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
WHERE ARQOS.CNPO=32838

but the error now is this:

[ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code = -104 Token unknown - line 12, column 1 WHERE

CodePudding user response：

You need to give this join subquery an alias :

JOIN (SELECT NOMEITEM, SUM (QUANTORIG) AS QTDTOTAL FROM MATEMPEN GROUP BY NOMEITEM) /add alias here/ JOIN CLIENTES ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE WHERE ARQOS.CNPO=32838