8 Replies Latest reply: Jul 18, 2012 8:11 AM by Frank Kulash RSS

    aggregation query

    949661
      Dear colleagues,

      I would like to create the query that:
      1 step: find the max of NSAVERAGE per day
      2 step: max of "1st step" per NE & LAC

      My tables:
      A. Table "SUB"
      Name Null? Type
      ----------------------- -------- ----------------
      MSC_ID NUMBER
      LAC_ID NUMBER
      PERIOD_START_TIME DATE
      PERIOD_DURATION NUMBER
      NSCURRENT NUMBER
      NSAVERAGE NUMBER

      B. Table NE_ID
      Name Null? Type
      ----------------------- -------- ----------------
      NE_ID NUMBER
      NE_NAME VARCHAR2(10)

      For the 1st step I created the following query:

      SELECT n.ne_name AS "MSS",
      s.LAC_ID,
      TO_CHAR(s.PERIOD_START_TIME,'DD.MM.YYYY') AS "DATE",
      MAX(s.NSAVERAGE) AS "MAX_SUB"
      FROM ne_id n JOIN SUB s
      ON (n.ne_id = s.msc_id)
      WHERE MOD(TO_CHAR(s.period_start_time,'J'),7)+1 ----- the weekend values are excluded
      IN (1,2,3,4,5)
      GROUP BY n.ne_name, s.LAC_ID, TO_CHAR(s.PERIOD_START_TIME,'DD.MM.YYYY')
      ORDER BY s.LAC_ID ASC;


      and now I'd like to create 2nd step: let's say, to get max of max from step1 grouped by ne_name & LAC
      Could you please help how to modify my query?

      Thanks
      Lukasz
        • 1. Re: aggregation query
          Purvesh K
          If you can help us with the sample data and the sample expected output.

          Please follow the link SQL and PL/SQL FAQ
          • 2. Re: aggregation query
            585159
            Dear Lukasz,
            Instead you may provide sample create table , insert script, & desired result for getting better answer.
            --
            You may use with clause to get max from step 1 in step 2
            e.g.
            with maxdata as( select max(a) from step1)
            select * from maxdata X, step2 Y;
            • 3. Re: aggregation query
              fjfranken
              I don't know if I understand correctly, but wouldn't it simply be:
              SELECT MSS, max(LAC_ID) FROM
              (
              SELECT n.ne_name AS "MSS",
              s.LAC_ID, 
              TO_CHAR(s.PERIOD_START_TIME,'DD.MM.YYYY') AS "DATE",
              MAX(s.NSAVERAGE) AS "MAX_SUB"
              FROM ne_id n JOIN SUB s
              ON (n.ne_id = s.msc_id)
              WHERE MOD(TO_CHAR(s.period_start_time,'J'),7)+1
              IN (1,2,3,4,5)
              GROUP BY n.ne_name, s.LAC_ID, TO_CHAR(s.PERIOD_START_TIME,'DD.MM.YYYY')
              )
              GROUP BY MSS;
              FJFranken
              • 4. Re: aggregation query
                949661
                this my source table:

                MSS     LAC_ID     PERIOD_START_TIME     NSAVERAGE
                M1     1     2012/07/06 12:00:00     100
                M1     1     2012/07/06 13:00:00     150
                M1     1     2012/07/06 14:00:00     200
                M1     1     2012/07/07 12:00:00     110
                M1     1     2012/07/07 13:00:00     100
                M1     1     2012/07/07 14:00:00     120
                M1     2     2012/07/06 12:00:00     100
                M1     2     2012/07/06 13:00:00     120
                M1     2     2012/07/06 14:00:00     200
                M1     2     2012/07/07 12:00:00     180
                M1     2     2012/07/07 13:00:00     160
                M1     2     2012/07/07 14:00:00     150
                M2     1     2012/07/06 12:00:00     100
                M2     1     2012/07/06 13:00:00     150
                M2     1     2012/07/06 14:00:00     200
                M2     1     2012/07/07 12:00:00     110
                M2     1     2012/07/07 13:00:00     100
                M2     1     2012/07/07 14:00:00     120
                M2     2     2012/07/06 12:00:00     100
                M2     2     2012/07/06 13:00:00     120
                M2     2     2012/07/06 14:00:00     200
                M2     2     2012/07/07 12:00:00     180
                M2     2     2012/07/07 13:00:00     160
                M2     2     2012/07/07 14:00:00     150

                so now in first step I would like to find max(NSAVERAGE) per day & exclude weekends

                MSS     LAC_ID     PERIOD_START_TIME     NSAVERAGE
                M1     1     2012/07/06          200
                M1     1     2012/07/07          120
                M1     2     2012/07/06          200
                M1     2     2012/07/07          180
                M2     1     2012/07/06          200
                M2     1     2012/07/07          120
                M2     2     2012/07/06          200
                M2     2     2012/07/07          180

                so far I was successful

                so in the second step I would like to have (max of max)

                MSS     LAC_ID          NSAVERAGE
                M1     1          200
                M1     2          200
                M2     1          200
                M2     2          200

                I hope now my description is more clear
                Once again thank you for your support!
                Lukasz
                • 5. Re: aggregation query
                  Frank Kulash
                  Hi, Lukasz,

                  Use the analytic RANK function to number the rows in DESCending order of the average. In a super-query, pick only the rows numbered 1.

                  If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could show you.
                  • 6. Re: aggregation query
                    pollywog
                    might be easier for people to work on your problem if you post your source data as either insert statments or a with t kind of construct.
                    WITH t
                         AS (SELECT 'M1' mss,
                                    1 lac,
                                    TO_DATE ('2012/07/06 12:00:00', 'yyyy/mm/dd HH24:MI:SS')
                                       period_start_time,
                                    100 nsaverage
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    1,
                                    TO_DATE ('2012/07/06 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    150
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    1,
                                    TO_DATE ('2012/07/06 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    200
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    1,
                                    TO_DATE ('2012/07/07 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    110
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    1,
                                    TO_DATE ('2012/07/07 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    100
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    1,
                                    TO_DATE ('2012/07/07 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    120
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/06 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    100
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/06 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    120
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/06 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    200
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/07 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    180
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/07 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    160
                               FROM DUAL
                             UNION ALL
                             SELECT 'M1',
                                    2,
                                    TO_DATE ('2012/07/07 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    150
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/06 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    100
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/06 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    150
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/06 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    200
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/07 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    110
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/07 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    100
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    1,
                                    TO_DATE ('2012/07/07 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    120
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/06 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    100
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/06 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    120
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/06 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    200
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/07 1200:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    180
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/07 1300:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    160
                               FROM DUAL
                             UNION ALL
                             SELECT 'M2',
                                    2,
                                    TO_DATE ('2012/07/07 1400:00', 'yyyy/mm/dd HH24:MI:SS'),
                                    150
                               FROM DUAL)
                    • 7. Re: aggregation query
                      Purvesh K
                      The below should help:-
                      with data(mss, lac_id, period_start, nsavg) as
                      (
                      SELECT 'M1',      1,     to_timestamp('2012/07/06 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M1',      1,     to_timestamp('2012/07/06 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     150 from dual
                      union all
                      SELECT 'M1',      1,     to_timestamp('2012/07/06 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     200 from dual
                      union all
                      SELECT 'M1',      1,     to_timestamp('2012/07/07 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     110 from dual
                      union all
                      SELECT 'M1',      1,     to_timestamp('2012/07/07 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M1',      1,     to_timestamp('2012/07/07 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     120 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/06 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/06 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     120 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/06 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     200 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/07 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     180 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/07 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     160 from dual
                      union all
                      SELECT 'M1',      2,     to_timestamp('2012/07/07 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     150 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/06 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/06 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     150 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/06 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     200 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/07 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     110 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/07 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M2',      1,     to_timestamp('2012/07/07 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     120 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/06 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     100 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/06 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     120 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/06 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     200 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/07 12:00:00', 'YYYY/MM/DD HH24:MI:SS'),     180 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/07 13:00:00', 'YYYY/MM/DD HH24:MI:SS'),     160 from dual
                      union all
                      SELECT 'M2',      2,     to_timestamp('2012/07/07 14:00:00', 'YYYY/MM/DD HH24:MI:SS'),     150 from dual
                      )
                      ,
                      max_nsavg as
                      (
                        select mss, lac_id, max(nsavg) over (partition by period_start) col
                          from data
                         where trim(to_char(period_start, 'DAY')) NOT IN ('SATURDAY', 'SUNDAY')
                      )
                      select mss, lac_id, max(col)
                        from max_nsavg
                       group by mss, lac_id;
                      • 8. Re: aggregation query
                        Frank Kulash
                        Hi,

                        Using the data that Pollywog posted:
                        WITH     got_rnk          AS
                        (
                             SELECT       mss, lac
                             ,       TRUNC (period_start_time)     AS start_day          -- If wanted
                             ,       MAX (nsaverage)          AS max_nsaverage
                             ,       RANK () OVER ( ORDER BY  MAX (nsaverage) DESC)
                                                                              AS rnk
                             FROM      t
                             WHERE       TO_CHAR ( period_start_time
                                           , 'Dy'
                                         , 'NLS_DATE_LANGUAGE=ENGLISH'
                                         )          NOT IN ('Sat', 'Sun')
                             GROUP BY  mss, lac, TRUNC (period_start_time)
                        )
                        SELECT       mss, lac, max_nsaverage
                        ,       start_day                                   -- If wanted
                        FROM       got_rnk
                        WHERE       rnk     = 1
                        ;
                        This inlcudes the date of each row in the result set.
                        MS        LAC MAX_NSAVERAGE START_DAY
                        -- ---------- ------------- ----------
                        M1          2           200 2012/07/06
                        M1          1           200 2012/07/06
                        M2          1           200 2012/07/06
                        M2          2           200 2012/07/06
                        If you don't want that, then there's no need to include start_day in either SELECT clause.

                        Analytic functions (such as RANK) are computed after the GROUP BY is done and aggregate functions (such as MAX, above) are computed, so you can nest aggregate functions inside analytic functions.

                        Edited by: Frank Kulash on Jul 18, 2012 9:10 AM