Total results by products in oracle table

Asked

Viewed 171 times

0

I have two tables similar to the tables below:

TGFCAB

|NUNOTA|   DTMOV  |
------------------|
|  1   |01/06/2019|
|  2   |02/06/2019|
|  3   |03/06/2019|


TGFITE
|NUNOTA|CODPROD|QTDNEG|VLRUNIT |VLRTOTAL|
|------|-------|------|--------|--------|
|  1   |  18   |5,2   |10      |52      |
|  1   |  26   |1,3   |10      |13      |
|  2   |  11   |10,5  |10      |105     |   
|  2   |  26   |16,5  |10      |165     |
|  2   |  18   |2,5   |10      |250     |
|  3   |  18   |2,0   |10      |200     |

I am not managing to perform a SELECT to obtain the total values of a given product, the sum of all total values and the total of traded items, for example.

I need to perform a select so that I have the following result.

|COD PROD|VLRTOTALPROD|
|18      |502         |
|26      |178         |
|11      |105         |
|TOTAL   |785         |

But I cannot filter even with the requests below.

SUM(QTDNEG) AS TOTALNEG,
SUM(CASE WHEN TGFITE.CODPROD = 18 THEN QTDNEG ELSE 0 END) AS TOTALPRODUM
SUM(CASE WHEN TGFITE.CODPROD = 26 THEN QTDNEG ELSE 0 END) AS TOTALPRODDO
SUM(CASE WHEN TGFITE.CODPROD = 11 THEN QTDNEG ELSE 0 END) AS TOTALPRODTR

The above information is only to illustrate, the real select is the below.

SELECT 
    DTMOV,
    TGFITE.NUNOTA,
    CODTIPOPER,
    TGFITE.CODEMP,
    NUMNOTA,
    DTNEG,
    QTDNEG,
    VLRUNIT,
    VLRTOT,
    CODVOL,
    PRODUTONFE,
    TGFCAB.DTMOV,
    SUM(QTDNEG) AS TOTALNEG,
    SUM(CASE WHEN TGFITE.PRODUTONFE = 190 THEN QTDNEG ELSE 0 END)
FROM 
        TGFCAB INNER JOIN TGFITE ON (TGFCAB.NUNOTA = TGFITE.NUNOTA) 
WHERE TGFCAB.DTMOV BETWEEN :PERINI AND :PERFIM 
        AND TGFCAB.CODTIPOPER IN (401, 18, 47, 103, 102, 36, 501)
GROUP BY DTMOV, TGFITE.NUNOTA, CODTIPOPER, TGFITE.CODEMP, NUMNOTA, DTNEG, QTDNEG, 
VLRUNIT, VLRTOT, CODVOL, PRODUTONFE, TGFCAB.DTMOV
database oracle pl-sql
  • 1

    wouldn’t it be simpler to group by product and add up? something like that: select CODPROD, sum(QTDNEG) group by CODPROD? only one suggestion, pq for each new product will have to change the query, and with the group no

  • @Ricardopunctual the problem is that this example is very minimalist to what I have to look for, my select has about 12 columns, I do not know how to do this, I will post below more or less as it is here,

  • 1

    What is the point of placing the QTDNEG field as one of the fields of the GROUP BY clause and also putting this field in the SUM aggregation function?

  • Because I need to group the items by the quantity negotiated, this is the quantity sold, ie if I have two sales of the product a, with quantities 10 and 10, I sold 20 units of the product a. This is the information I need.

  • 1

    Check out the Oracle’s Analytic Function https://oracle-base.com/articles/misc/analytic-functions

No answers

Browser other questions tagged database oracle pl-sql

You are not signed in. Login or sign up in order to post.