This discussion is archived
8 Replies Latest reply: Jul 18, 2012 6:11 AM by Frank Kulash RSS

aggregation query

949661 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points