Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: select query counts

    Hi,

    Please help me to resolve bleow questions

    DB2 9.1 Z/OS

    1.How can i get the maximum count ?

    Code:
    SELECT COUNT(*) AS CNT,work_DATE
    FROM (
    SELECT PLT_NO,no_FIRST,WORK_DATE
    FROM BASE_TABLE
    
    WHERE
     DATA_REF='WEST'
    )A
    GROUP BY 
    WORK_DATE 
    
    BASE_TABLE
    
    PLT_NO  NO_FIRST  WORK_DATE   DATA_REF
    1AA      001      2013-03-15   WEST  
    AAA      111      2012-03-21   WEST
    AAA      111      2012-03-21   WEST
    AAA      111      2012-03-21   WEST
    AAA      111      2012-03-21   WEST
    BBB      222      2011-02-11   WEST
    BBB      222      2011-02-11   WEST
    CCC      333      2010-02-11   WEST
    
    
    GETTING RESULT SET
    
    CNT     WORK_DATE
    1       2013-03-15
    4       2012-03-21
    2       2011-02-11
    1       2010-02-11 
    
     
    
    EXPECTED RESULT SET
    
    CNT     WORK_DATE
    4       2012-03-21
    2.How to sum the each records counts ?

    Code:
    select COUNT(*)AS CNT, PLT_NO FROM
    
    (SELECT  distinct PLT_NO,no_FIRST from BASE_TABLE
    
    )A 
    GROUP BY 
    PLT_NO
    
    
    
    BASE_TABLE
    
    PLT_NO  NO_FIRST   
    1AA      001       
    AAA      111       
    AAA      111      
    AAA      222       
    AAA      333       
    BBB      111       
    BBB      222       
    CCC      333 
    CCC      333
      
    
     
    GETTING RESULT SET 
    
    CNT    PLT_NO
    1       1AA
    3       AAA
    2       BBB
    1       CCC
    
    But i want the summation of all PLT_no counts
    
    EXPECTED RESULT SET
    
    CNT    
    
    7
    3.Some one deleted the records in the table unknowingly,How to find who is deleted the records.
    Which table is getting the information

    4.How to identify the person who is accessing the table ?


    Thanks,

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    1. Maximum Count.

    You were close. These 2 queries are identical in their functionality. You just need to choose which you like.

    Code:
    ;with cte as
    (
       SELECT COUNT(*) AS CNT, PLT_NO, Work_Date
         FROM BASE_TABLE
         GROUP BY Work_Date, PLT_NO
    )
    SELECT MAX(cte.cnt), MIN(cte.Work_Date)
      FROM cte
      
    
    SELECT MAX(a.cnt), MIN(a.Work_Date)
      FROM 
        (SELECT COUNT(*) AS CNT, PLT_NO, Work_Date
           FROM BASE_TABLE
          GROUP BY Work_Date, PLT_NO
         ) a
    2. Sum Each Record Count

    Given the limited data, both these queries return the same thing. Though I don't think the second one is what you wanted. Modify it for your situation.

    Code:
    ;with cte as
     (
        select COUNT(*) AS CNT
          FROM BASE_TABLE
          group by PLT_NO
     )
     Select SUM(cte.cnt) from cte
    
    
     Select COUNT(*) from BASE_TABLE
    I will leave questions 3 & 4 to those who are more knowledgeable about DB2's logging & auditing utilities.

    I did Google the topic and found this:

    3. Who deleted records?

    The DB2 logs can be found in the directory specified in your DB CFG
    parameter "Path to log files". . .
    You could use the log reader API to access those log file. However, the API
    (http://publib.boulder.ibm.com/infoce...ic/com.ibm.db2....)
    does not explain each and every bit in the log records. The log record can
    and does change with new requirements being implemented in DB2. . .
    The logs don't tell you _who_ deleted data. You could only figure out when
    that happened.

    I guess what you really need (or had needed) is some auditing facility.

    --
    Knut Stolze
    DB2 z/OS Utilities Development
    IBM Germany

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    My query for the first question is NOT correct. Using MIN(date) returns the smallest date from the inner query.

    I need to rethink my approach to this one.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1. How can i get the maximum count ?

    Example 1: Tested on DB2 9.7.5 for Windows.
    But, I couldn't find any reason not to work on DB2 9.1 for z/OS,
    except BASE_TABLE common-table-expression which was used to generate test data.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     BASE_TABLE
    ( PLT_NO , NO_FIRST , WORK_DATE , DATA_REF ) AS (
    VALUES
      ( '1AA' , 001 , DATE('2013-03-15') , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'BBB' , 222 , '2011-02-11' , 'WEST' )
    , ( 'BBB' , 222 , '2011-02-11' , 'WEST' )
    , ( 'CCC' , 333 , '2010-02-11' , 'WEST' )
    )
    SELECT cnt
         , WORK_DATE
     FROM  (SELECT WORK_DATE
                 , COUNT(*) AS cnt
                 , ROW_NUMBER()
                      OVER( ORDER BY COUNT(*) DESC ) AS rnum
             FROM  BASE_TABLE
             WHERE DATA_REF = 'WEST'
             GROUP BY
                   WORK_DATE
           ) AS s
     WHERE rnum = 1
    ;
    ------------------------------------------------------------------------------
    
    CNT         WORK_DATE 
    ----------- ----------
              4 2012-03-21
    
      1 record(s) selected.

    2.How to sum the each records counts ?

    Example 2: Tested on DB2 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     BASE_TABLE
    ( PLT_NO , NO_FIRST ) AS (
    VALUES
      ( '1AA' , 001 )
    , ( 'AAA' , 111 )
    , ( 'AAA' , 111 )
    , ( 'AAA' , 222 )
    , ( 'AAA' , 333 )
    , ( 'BBB' , 111 )
    , ( 'BBB' , 222 )
    , ( 'CCC' , 333 )
    , ( 'CCC' , 333 )
    )
    SELECT SUM(cnt) AS cnt
     FROM  (SELECT PLT_NO
                 , COUNT(distinct NO_FIRST) AS cnt
             FROM  BASE_TABLE
             GROUP BY 
                   PLT_NO
           ) A
    ;
    ------------------------------------------------------------------------------
    
    CNT        
    -----------
              7
    
      1 record(s) selected.
    Example 3: Tested on DB2 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     BASE_TABLE
    ( PLT_NO , NO_FIRST ) AS (
    VALUES
      ( '1AA' , 001 )
    , ( 'AAA' , 111 )
    , ( 'AAA' , 111 )
    , ( 'AAA' , 222 )
    , ( 'AAA' , 333 )
    , ( 'BBB' , 111 )
    , ( 'BBB' , 222 )
    , ( 'CCC' , 333 )
    , ( 'CCC' , 333 )
    )
    SELECT COUNT(distinct PLT_NO || CHAR(NO_FIRST) ) AS cnt
     FROM  BASE_TABLE
    ;
    ------------------------------------------------------------------------------
    
    CNT        
    -----------
              7
    
      1 record(s) selected.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1. How can i get the maximum count ?

    Example 4: Tested on DB2 9.7.5 for Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     BASE_TABLE
    ( PLT_NO , NO_FIRST , WORK_DATE , DATA_REF ) AS (
    VALUES
      ( '1AA' , 001 , DATE('2013-03-15') , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'AAA' , 111 , '2012-03-21' , 'WEST' )
    , ( 'BBB' , 222 , '2011-02-11' , 'WEST' )
    , ( 'BBB' , 222 , '2011-02-11' , 'WEST' )
    , ( 'CCC' , 333 , '2010-02-11' , 'WEST' )
    )
    SELECT COUNT(*) AS cnt
         , WORK_DATE
     FROM  BASE_TABLE
     WHERE DATA_REF = 'WEST'
     GROUP BY
           WORK_DATE
     ORDER BY
           cnt DESC
     FETCH FIRST 1 ROW ONLY
    ;
    ------------------------------------------------------------------------------
    
    CNT         WORK_DATE 
    ----------- ----------
              4 2012-03-21
    
      1 record(s) selected.

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Thanks all,Questions 1 and 2 are working good as per tonkuma way.could someone please answer the 3 and 4 ?

  7. #7
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by LinksUp View Post
    My query for the first question is NOT correct. Using MIN(date) returns the smallest date from the inner query.
    I was close too!

    Code:
    ;with cte as
    (
       SELECT COUNT(*) AS CNT, Work_Date as WorkDate,
              ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as RN
         FROM BASE_TABLE
        WHERE Data_Ref = 'WEST'
        GROUP BY Work_Date
    )
    SELECT cnt, workdate
      FROM cte
      WHERE rn = 1
    I was just missing the ROW_NUMBER window function. It seems that function comes into play quite often.
    Last edited by LinksUp; 05-16-13 at 16:24.

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Thanks, Any updates on Question 3 and 4 ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •