ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

sum grandtotal per code article (ciusmd)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • sum grandtotal per code article (ciusmd)

    this is my query:
    select NFREFC, nmff01, ciusmd, nmismd, sum(QRCEGP) as qtd
    from swapfilio.gcffc join swapfilio.gcffp
    on SOCEFC = SOCEFP
    and NFREFC = FRNEFP
    and NRFEFC = NRFEFP
    join swapfilio.gcfgp
    on SOCEGP = SOCEFP
    and ARMEGP = ARMEFP
    and AGREGP = AGREFP
    and NGREGP = NGREFP
    and NLNEGP = NLNEFP
    and ARTEGP = ARTEFP
    and MODEGP = MODEFP
    and EMBEGP = EMBEFP
    and GFMEGP = GFMEFP
    join swapfilio.gesmd
    on socsmd = SOCEGP
    and niismd = ARTEGP
    left join swapfilio.for01
    on NFOF01 = NFREFC
    and SOCF01 = SOCEFC
    where SOCF01 = '1'
    and SOCEFC = '1'
    and socsmd = '1'
    and (ciusmd = '5179627' or ciusmd = '6191569')
    and DFCEFC >= 20180801
    group by NFREFC, nmff01, ciusmd, nmismd

    Result:

    NFREFC NMFF01 CIUSMD NMISMD QTD
    802 OCP PORTUGAL - PROD FARM, SA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000
    801 BOTELHO & RODRIGUES LDA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000
    803 COOPROFAR - COOP PROPRIET FARM CRL 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 1.000
    1825 GFPH-PHARMA,LDA. 5179627 ASACOL 800 MG COMP.GR X60 160.000
    802 OCP PORTUGAL - PROD FARM, SA 5179627 ASACOL 800 MG COMP.GR X60 92.000
    804 UDIFAR II DISTRI FARMACÊUTICA SA 5179627 ASACOL 800 MG COMP.GR X60 1.000
    803 COOPROFAR - COOP PROPRIET FARM CRL 5179627 ASACOL 800 MG COMP.GR X60 11.000

    What i want:

    NFREFC NMFF01 CIUSMD NMISMD QTD
    802 OCP PORTUGAL - PROD FARM, SA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000 5
    801 BOTELHO & RODRIGUES LDA 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 2.000 5
    803 COOPROFAR - COOP PROPRIET FARM CRL 6191569 ACCU-CHECK SAFE-T-PRO UNO LANCETAS 1.000 5
    1825 GFPH-PHARMA,LDA. 5179627 ASACOL 800 MG COMP.GR X60 160.000 264
    802 OCP PORTUGAL - PROD FARM, SA 5179627 ASACOL 800 MG COMP.GR X60 92.000 264
    804 UDIFAR II DISTRI FARMACÊUTICA SA 5179627 ASACOL 800 MG COMP.GR X60 1.000 264
    803 COOPROFAR - COOP PROPRIET FARM CRL 5179627 ASACOL 800 MG COMP.GR X60 11.000 264

    I dont now what to get the expected result above, perhaps a subquery?
    Please help me

    Thank you

  • #2
    I assume the missing value is the count of records? If it is, just add COUNT(*) at the end

    Comment


    • #3
      Ok - thanks for the clarification on the private message. I'd start by simplifying the query, the first step is to create a view and then perform the query against this view. To accomplish what you want I believe the following will work.

      Code:
      CREATE OR REPLACE VIEW QTEMP/SWAPDTA AS (
      SELECT Nfrefc,
             Nmff01,
             Ciusmd,
             Nmismd,
             SUM(Qrcegp) AS Qtd
        FROM Swapfilio.Gcffc
        JOIN Swapfilio.Gcffp 
        ON 
          Socefc = Socefp AND
          Nfrefc = Frnefp AND
          Nrfefc = Nrfefp
        JOIN Swapfilio.Gcfgp 
        ON 
          Socegp = Socefp AND
          Armegp = Armefp AND
          Agregp = Agrefp AND 
          Ngregp = Ngrefp AND
          Nlnegp = Nlnefp AND
          Artegp = Artefp AND
          Modegp = Modefp AND
          Embegp = Embefp AND
          Gfmegp = Gfmefp
        JOIN Swapfilio.Gesmd 
        ON 
          Socsmd = Socegp AND
          Niismd = Artegp 
        LEFT JOIN Swapfilio.For01 
        ON 
          Nfof01 = Nfrefc AND
          Socf01 = Socefc
        WHERE Socf01 = '1' AND
              Socefc = '1' AND
              Socsmd = '1' AND
              (Ciusmd = '5179627' OR
              Ciusmd = '6191569') AND
              Dfcefc >= 20180801
        GROUP BY Nfrefc,
                 Nmff01,
                 Ciusmd,
                 Nmismd);        ;
      
        WITH F1 AS (
           SELECT Ciusmd, SUM(Qrcegp) AS TotCiusmd
           GROUP BY Ciusmd
        FROM QTEMP/SWAPDTA
        )
        SELECT A.*,F1.TotCiusmd 
        FROM QTEMP/SWAPDTA A 
        JOIN F1 
        ON a.Ciusmd = F1.Ciusmd;

      Comment


      • #4
        Sorry - I got one of the queries wrong...

        Code:
        CREATE OR REPLACE VIEW QTEMP/SWAPDTA AS (
        SELECT Nfrefc,
               Nmff01,
               Ciusmd,
               Nmismd,
               SUM(Qrcegp) AS Qtd
          FROM Swapfilio.Gcffc
          JOIN Swapfilio.Gcffp 
          ON 
            Socefc = Socefp AND
            Nfrefc = Frnefp AND
            Nrfefc = Nrfefp
          JOIN Swapfilio.Gcfgp 
          ON 
            Socegp = Socefp AND
            Armegp = Armefp AND
            Agregp = Agrefp AND 
            Ngregp = Ngrefp AND
            Nlnegp = Nlnefp AND
            Artegp = Artefp AND
            Modegp = Modefp AND
            Embegp = Embefp AND
            Gfmegp = Gfmefp
          JOIN Swapfilio.Gesmd 
          ON 
            Socsmd = Socegp AND
            Niismd = Artegp 
          LEFT JOIN Swapfilio.For01 
          ON 
            Nfof01 = Nfrefc AND
            Socf01 = Socefc
          WHERE Socf01 = '1' AND
                Socefc = '1' AND
                Socsmd = '1' AND
                (Ciusmd = '5179627' OR
                Ciusmd = '6191569') AND
                Dfcefc >= 20180801
          GROUP BY Nfrefc,
                   Nmff01,
                   Ciusmd,
                   Nmismd);        ;
        
          WITH F1 AS (
             SELECT Ciusmd, SUM(Qrcegp) AS TotCiusmd
          FROM QTEMP/SWAPDTA
          GROUP BY Ciusmd
          )
          SELECT A.*,F1.TotCiusmd 
          FROM QTEMP/SWAPDTA A 
          JOIN F1 
          ON a.Ciusmd = F1.Ciusmd;

        Comment


        • #5
          Since I don't have the files - I'm thinking this is a solution that doesn't require the view.

          Code:
          wiht f1 as (
          SELECT Nfrefc,
                 Nmff01,
                 Ciusmd,
                 Nmismd,
                 SUM(Qrcegp) AS Qtd
            FROM Swapfilio.Gcffc
            JOIN Swapfilio.Gcffp 
            ON 
              Socefc = Socefp AND
              Nfrefc = Frnefp AND
              Nrfefc = Nrfefp
            JOIN Swapfilio.Gcfgp 
            ON 
              Socegp = Socefp AND
              Armegp = Armefp AND
              Agregp = Agrefp AND 
              Ngregp = Ngrefp AND
              Nlnegp = Nlnefp AND
              Artegp = Artefp AND
              Modegp = Modefp AND
              Embegp = Embefp AND
              Gfmegp = Gfmefp
            JOIN Swapfilio.Gesmd 
            ON 
              Socsmd = Socegp AND
              Niismd = Artegp 
            LEFT JOIN Swapfilio.For01 
            ON 
              Nfof01 = Nfrefc AND
              Socf01 = Socefc
            WHERE Socf01 = '1' AND
                  Socefc = '1' AND
                  Socsmd = '1' AND
                  (Ciusmd = '5179627' OR
                  Ciusmd = '6191569') AND
                  Dfcefc >= 20180801
            GROUP BY Nfrefc,
                     Nmff01,
                     Ciusmd,
                     Nmismd),       
            F2 AS (
               SELECT Ciusmd, SUM(Qrcegp) AS TotCiusmd
            FROM F1
            GROUP BY Ciusmd
            )
            SELECT F1.*,F2.TotCiusmd 
            FROM F1
            JOIN F2 
            ON a.Ciusmd = F1.Ciusmd;

          Comment


          • #6
            i messed up on the last - change:
            Code:
             
               SELECT F1.*,F2.TotCiusmd    FROM F1   JOIN F2    ON a.Ciusmd = F1.Ciusmd;
            to
            Code:
               
               SELECT F1.*,F2.TotCiusmd    FROM F1   JOIN F2    ON F1.Ciusmd = F2.Ciusmd;

            Comment

            Working...
            X