CODEWITHSUNDEEP
sqloracle-databaseaggregate-functionsoracle9i
SeanD
SeanD

Reputation: 11

Aggregating Data in SQL

When looking at the picture in the link I need the Imploded Units to Average and the Exploded Units to Sum, I will attach my code below the Results shown below. I have been searching for an answer to this for a few days. I fear I may be trying to code over my head a little bit. 

   SELECT A.to_load_id                                   AS "Cases", 
   A.from_qty - A.to_qty                          AS "Imploded Units", 
   ( A.from_qty - A.to_qty ) * Nvl(SUM(p.qty), 1) AS "Exploded Units", 
   A.wskusku                                      AS "Sku's", 
   A.wave                                         AS "Wave", 
   A.from_loc                                     AS "Processed Location", 
   A.free_form_text                               AS "Zone" ,

   FROM   audits A, 
   prepack P 
   WHERE  A.from_loc LIKE 'S%' 
   AND A.to_loc = A.from_loc 
   AND free_form_text IN ( '01', '02', '03', '04', 
                           '05', '06', '07', '08', 
                           '09', '10', '11', '12', 
                           '13', '14' ) 
   AND A.wskusku = p.sku (+) 
   AND A.from_load_id = '42419472' 
   AND A.wave in ('WC055193','','','','','','','','','')
   AND To_date(Substr(a.date_wms, 1, 12), 'YYYY/MM/DD HH24:MI') >= 
       SYSDATE - 4 
   GROUP  BY A.to_load_id, 
      A.from_qty, 
      A.to_qty, 
      P.qty, 
      A.wskusku, 
      A.wave, 
      A.from_loc, 
      A.free_form_text

Upvotes: 1

Views: 229

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

You need to use avg() / sum() and not GROUP BY columns that are involved in aggregates:

SELECT A.to_load_id                                    AS "Cases"
      ,avg(A.from_qty - A.to_qty)                      AS "Imploded Units"
      ,sum(A.from_qty - A.to_qty) * Nvl(SUM(P.qty), 1) AS "Exploded Units"
      ,A.wskusku                                       AS "Sku's"
      ,A.wave                                          AS "Wave"
      ,A.from_loc                                      AS "Processed Location"
      ,A.free_form_text                                AS "Zone"
FROM   audits A 
JOIN   prepack P ON P.sku (+) = A.wskusku
WHERE  A.from_loc LIKE 'S%' 
AND    A.to_loc = A.from_loc 
AND    A.free_form_text IN ( '01', '02', '03', '04', 
                           '05', '06', '07', '08', 
                           '09', '10', '11', '12', 
                           '13', '14' )
AND    A.from_load_id = '42419472'
AND    A.wave in ('WC055193','','','','','','','','','')
AND    To_date(Substr(A.date_wms, 1, 12), 'YYYY/MM/DD HH24:MI') >= SYSDATE - 4
GROUP  BY A.to_load_id, 
      ,A.wskusku
      ,A.wave
      ,A.from_loc
      ,A.free_form_text

Not sure why you multiple the "Exploded Units", but not the "Imploded Units". I copied what you have there.

Upvotes: 1

Related Questions