1 2 Previous Next 21 Replies Latest reply: Apr 2, 2013 8:20 PM by Frank Kulash RSS

    Query with conditional Counting Columns

    993527
      Hello again,

      i need your help again.
      Sample data:
      DROP     TABLE     TABLE_1;
      
      CREATE      TABLE      TABLE_1
         
      (     "DEPT"           VARCHAR2 (1)
      ,      "PD"           VARCHAR2 (2)
      ,      "INMPD"      VARCHAR2 (5)
      ,     "AGE"          NUMBER
      );
      
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '12',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '12',     'TRUE',          TO_NUMBER('15')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '12',     'TRUE',          TO_NUMBER('8')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '12',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '12',     'TRUE',          TO_NUMBER('15')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '12',     'TRUE',          TO_NUMBER('8')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '05',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '05',     'FALSE',     TO_NUMBER('15')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '05',     'TRUE',          TO_NUMBER('8')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '05',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '05',     'FALSE',     TO_NUMBER('15')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '05',     'TRUE',          TO_NUMBER('8')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '02',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '02',     'TRUE',          TO_NUMBER('2')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('A',               '02',     'TRUE',          TO_NUMBER('3')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '02',     'FALSE',     TO_NUMBER('31')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '02',     'TRUE',          TO_NUMBER('2')     );
      INSERT      INTO      TABLE_1      (DEPT,               PD,      INMPD,          AGE)
                          VALUES           ('S',               '02',     'TRUE',          TO_NUMBER('3')     );
      And my Query:
      SELECT           T1.DEPT
      ,               CASE
                          WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('01') AND TO_NUMBER('04')
                          AND          T1.INMPD = 'TRUE'
                          THEN     COUNT(*)
                     END                               AS     "PD02"
      ,               CASE
                          WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('05') AND TO_NUMBER('08')
                          AND          T1.INMPD = 'TRUE'
                          THEN     COUNT(*)
                     END                               AS     "PD05"
      ,               CASE
                          WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('09') AND TO_NUMBER('15')
                          AND          T1.INMPD = 'TRUE'
                          THEN     COUNT(*)
                     END                               AS     "PD12"
      ,               COUNT(*)                         AS     "TOTAL"
      FROM     TABLE_1     T1
      GROUP BY     T1.DEPT
      ,               T1.INMPD
      ,               T1.PD
      ORDER BY     T1.DEPT 
      This query gives me for each Priority a new Row.
      But i want one Row for one DEPT like this:
      DEPT                                                PD02                                                PD05                                                PD12                                               TOTAL
      ---- --------------------------------------------------- --------------------------------------------------- --------------------------------------------------- ---------------------------------------------------
      A                                                      2                                                   1                                                   2                                                   9
      S                                                      2                                                   1                                                   2                                                   9
      In Row PD02 should be counted if the PD is between 01 and 04 and INMPD is TRUE.
      In Row PD05 should be counted if the PD is between 05 and 08 and INMPD is TRUE.
      In Row PD12 should be counted if the PD is between 09 and 15 and INMPD is TRUE.
      In Row TOTAL should be the total amount of Values for the DEPT.

      Thank you all.

      Reini
        • 1. Re: Query with conditional Counting Columns
          Karthick_Arp
          Like this?
          SQL> select dept, max(pd02), max(pd05), max(pd12), sum(total)
            2    from (
            3  SELECT             T1.DEPT
            4  ,                  CASE
            5                             WHEN    TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('01') AND TO_NUMBER('04')
            6                             AND             T1.INMPD = 'TRUE'
            7                             THEN    COUNT(*)
            8                     END                                             AS      "PD02"
            9  ,                  CASE
           10                             WHEN    TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('05') AND TO_NUMBER('08')
           11                             AND             T1.INMPD = 'TRUE'
           12                             THEN    COUNT(*)
           13                     END                                             AS      "PD05"
           14  ,                  CASE
           15                             WHEN    TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('09') AND TO_NUMBER('15')
           16                             AND             T1.INMPD = 'TRUE'
           17                             THEN    COUNT(*)
           18                     END                                             AS      "PD12"
           19  ,                  COUNT(*)                                        AS      "TOTAL"
           20  FROM       TABLE_1 T1
           21  GROUP BY   T1.DEPT
           22  ,                  T1.INMPD
           23  ,                  T1.PD
           24  ORDER BY   T1.DEPT
           25  )
           26  group by dept
           27  /
           
          D  MAX(PD02)  MAX(PD05)  MAX(PD12) SUM(TOTAL)
          - ---------- ---------- ---------- ----------
          A          2          1          2          9
          S          2          1          2          9
           
          SQL> 
          • 2. Re: Query with conditional Counting Columns
            Mahir M. Quluzade
            Hi

            try this please
            SELECT           T1.DEPT
            ,               SUM(CASE
                                  WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('01') AND TO_NUMBER('04')
                                  AND          T1.INMPD = 'TRUE'
                                 THEN     1
                           END)                               AS     "PD02"
            ,          SUM(CASE
                                WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('05') AND TO_NUMBER('08')
                                AND          T1.INMPD = 'TRUE'
                                THEN     1
                           END)                               AS     "PD05"
            ,          SUM(CASE
                                WHEN        TO_NUMBER(T1.PD) BETWEEN TO_NUMBER('09') AND TO_NUMBER('15')
                                AND          T1.INMPD = 'TRUE'
                                THEN     1
                           END)                               AS     "PD12"
            ,               COUNT(*)                         AS     "TOTAL"
            FROM     TABLE_1     T1
            GROUP BY     T1.DEPT;
            Mahir M. Quluzade
            • 3. Re: Query with conditional Counting Columns
              Frank Kulash
              Hi, Reini,

              Here's one way:
              SELECT    dept
              ,       COUNT ( CASE 
                                WHEN TO_NUMBER (pd) BETWEEN  1  
                                                 AND      4
                              AND  inmpd = 'TRUE'          THEN  1  
                           END
                        )     AS pd02
              ,       COUNT ( CASE 
                                WHEN TO_NUMBER (pd) BETWEEN  5 
                                                 AND      8
                              AND  inmpd = 'TRUE'          THEN  1  
                           END
                        )     AS pd05
              ,       COUNT ( CASE 
                                WHEN TO_NUMBER (pd) BETWEEN  9  
                                                 AND     15
                              AND  inmpd = 'TRUE'          THEN  1  
                           END
                        )     AS pd12
              ,       COUNT (*)     AS total 
              FROM       table_1
              GROUP BY  dept
              ORDER BY  dept
              ;
              Is pd always a number? If so, why not use a NUMBER column? If not, do you want the row to count toward the TOTAL column, but not any of the others?
              • 4. Re: Query with conditional Counting Columns
                993527
                Hi Frank,

                thank you.
                Yes PD looks always like a number, but the Problem at this number is it has to be with a leading Zero. And iam not the Person how created the database, i only the one how pull data out of it.

                You solution Looks pretty perfect. but it was to easy. I have expected it is not necessary, to post the whole sample table from which it get the data, but now it Looks like i have to post it.

                Data:
                CREATE      TABLE      TABLE_2
                   
                (     "ORDER_NR"      VARCHAR2 (12)
                ,      "PRIORITY"      VARCHAR2 (2)
                ,      "WO_STEP"      VARCHAR2 (1)
                ,      "STEP_DATE"      DATE
                );
                
                CREATE      TABLE      TABLE_1
                (     "ORDER_NR"           VARCHAR2     (12) PRIMARY KEY
                ,      "PRIORITY"           VARCHAR2      (2)
                ,     "DEPT"               VARCHAR2 (1)
                ,      "CREATE_DATE"      DATE
                ,     "ACT_STEP"          VARCHAR2     (2)
                ,     "STEP_DATE"          DATE
                ,     "EMPLOYEE"          VARCHAR2     (5)
                ,     "DESCRIPTION"     VARCHAR2     (20)
                );
                
                INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,           PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                                    VALUES           ('1KKA1T205634',     'A',               '12',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0010',     'CLEAN HOUSE');
                INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                                    VALUES           ('1KKA1Z300612',     'S',               '02',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0052',     'REPAIR CAR');
                INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,      CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION) 
                                    VALUES           ('1KKA1T205635',     'A',               '05',          TO_DATE('10-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'),     'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'),     'W0012',     'CLEAN HOUSE');
                INSERT      INTO      TABLE_1      (ORDER_NR,               DEPT,          PRIORITY,     CREATE_DATE,                                                        ACT_STEP,     STEP_DATE,                                                            EMPLOYEE,     DESCRIPTION)
                                    VALUES           ('1KKA1Z300613',     'A',               '12',          TO_DATE('08-FEB-13 14:00:00','DD-MON-RR HH24:MI:SS'),     'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'),     'K0053',     'REPAIR CAR');
                
                
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          '5',          TO_DATE('13-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'K',          TO_DATE('13-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          '5',          TO_DATE('13-FEB-13 11:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'K',          TO_DATE('13-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          '5',          TO_DATE('13-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'C',          TO_DATE('14-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'B',          TO_DATE('14-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'E',          TO_DATE('18-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'S',          TO_DATE('21-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205634',     '12',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          '5',          TO_DATE('13-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'K',          TO_DATE('13-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          '5',          TO_DATE('13-FEB-13 11:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'K',          TO_DATE('13-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          '5',          TO_DATE('13-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'C',          TO_DATE('14-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'B',          TO_DATE('14-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'E',          TO_DATE('18-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300612',     '02',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'A',          TO_DATE('12-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          '5',          TO_DATE('13-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'K',          TO_DATE('13-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          '5',          TO_DATE('13-FEB-13 11:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'K',          TO_DATE('13-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          '5',          TO_DATE('13-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'C',          TO_DATE('14-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'B',          TO_DATE('14-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'E',          TO_DATE('18-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'S',          TO_DATE('21-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'R',          TO_DATE('21-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1T205635',     '05',          'U',          TO_DATE('28-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'A',          TO_DATE('12-FEB-13 13:52:42','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          '5',          TO_DATE('13-FEB-13 09:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'K',          TO_DATE('13-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          '5',          TO_DATE('13-FEB-13 11:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'K',          TO_DATE('13-FEB-13 12:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          '5',          TO_DATE('13-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'C',          TO_DATE('14-FEB-13 08:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'B',          TO_DATE('14-FEB-13 10:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'E',          TO_DATE('18-FEB-13 13:00:00','DD-MON-RR HH24:MI:SS'));
                INSERT     INTO      TABLE_2      (ORDER_NR,               PRIORITY,     WO_STEP,     STEP_DATE)
                                    VALUES           ('1KKA1Z300613',     '12',          'F',          TO_DATE('20-FEB-13 16:00:00','DD-MON-RR HH24:MI:SS'));
                COMMIT;
                And the Query for the RAW data:
                with t2 as (
                            select  t.*,
                                    lead(step_date) over(partition by order_nr order by step_date) next_step_date
                              from  table_2 t
                           )
                select  t1.DEPT
                ,          t1.PRIORITY
                ,       nvl(
                            max(
                                case t2.wo_step
                                  when 'U' then t2.step_date
                                end
                               ),
                           sysdate
                          ) - t1.create_date age_1,
                        nvl(
                            max(
                                case t2.wo_step
                                  when 'R' then t2.step_date
                                end
                               ),
                           sysdate
                          ) - MIN(
                                          CASE 
                                         WHEN     t2.wo_step = 'A'
                                         THEN     t2.step_date
                                         END
                               ) age_2
                ,          CASE
                               WHEN      t1.priority BETWEEN 1 AND 3
                               AND          nvl(
                                           max(
                                               case t2.wo_step
                                                 when 'U' then t2.step_date
                                               end
                                                   ),
                                                    sysdate
                                         ) - MIN(
                                          CASE 
                                         WHEN     t2.wo_step = 'A'
                                         THEN     t2.step_date
                                         END
                               ) >= 5
                               THEN     'FALSE'
                               WHEN      t1.priority BETWEEN 4 AND 8
                               AND          nvl(
                                           max(
                                               case t2.wo_step
                                                 when 'U' then t2.step_date
                                               end
                                                   ),
                                                    sysdate
                                         ) - MIN(
                                          CASE 
                                         WHEN     t2.wo_step = 'A'
                                         THEN     t2.step_date
                                         END
                               ) >= 8
                               THEN     'FALSE'
                               WHEN      t1.priority BETWEEN 9 AND 15
                               AND          nvl(
                                           max(
                                               case t2.wo_step
                                                 when 'U' then t2.step_date
                                               end
                                                   ),
                                                    sysdate
                                         ) - MIN(
                                          CASE 
                                         WHEN     t2.wo_step = 'A'
                                         THEN     t2.step_date
                                         END
                               ) >= 30
                               THEN     'FALSE'
                               ELSE     'TRUE'
                          END AS "INMPD"                                             
                  from  table_1 t1,
                        t2
                  where t2.order_nr = t1.order_nr
                  group by      t1.DEPT
                  ,               t1.PRIORITY
                  ,               t1.CREATE_DATE
                From the sample table above i Need the data that i mentioned before:
                In Row PD02 should be counted if the PD is between 01 and 04 and INMPD is TRUE.
                In Row PD05 should be counted if the PD is between 05 and 08 and INMPD is TRUE.
                In Row PD12 should be counted if the PD is between 09 and 15 and INMPD is TRUE.
                In Row TOTAL should be the total amount of Values for the DEPT.

                The Total row should show the total ORDER's per DEPT.

                I habe tried to modify the Query that i posted above to the COUNT statement that Frank postet, but i always get a single Group error.
                I searched in this Forum and found out, it is not possible to put multiple case Statements in a COUNT function.
                How can i solve that issue?

                Thanks to all.
                • 5. Re: Query with conditional Counting Columns
                  Frank Kulash
                  Hi,

                  Always post the results you want from the sample data.

                  Descibing the results is good, but describe them in addition to, not instead of, actually posting them.
                  • 6. Re: Query with conditional Counting Columns
                    993527
                    Hi Frank,

                    sorry i forgot that i posted other sample data.
                    Here what i expect:
                    DEPT                                                PD02                                                PD05                                                PD12                                               TOTAL
                    ---- --------------------------------------------------- --------------------------------------------------- --------------------------------------------------- ---------------------------------------------------
                    A                                                      0                                                   0                                                   2                                                   3
                    S                                                      0                                                   0                                                   0                                                   1
                    Thank You.
                    • 7. Re: Query with conditional Counting Columns
                      Frank Kulash
                      Hi,
                      Freakster235 wrote:
                      Hi Frank,

                      sorry i forgot that i posted other sample data.
                      Here what i expect:
                      Why do you post things like this?
                      DEPT                                                PD02                                                PD05                                                PD12                                               TOTAL
                      ---- --------------------------------------------------- --------------------------------------------------- --------------------------------------------------- ---------------------------------------------------
                      A                                                      0                                                   0                                                   2                                                   3
                      S                                                      0                                                   0                                                   0                                                   1
                      Do you find it easier to read and understand the results you posted above, or the result set below?
                      DEPT  PD02  PD05  PD12  TOTAL
                      ----  ----  ----  ----  -----  
                      A        0     0     2      3
                      S        0     0     0      1
                      Which do you think lead to your problem getting solved faster and more accurately?
                      Freakster235 wrote:
                      ... And the Query for the RAW data:
                      with t2 as (
                      select  t.*,
                      lead(step_date) over(partition by order_nr order by step_date) next_step_date
                      from  table_2 t
                      )
                      What is the point of sub-query t2? Why compute next_step_date if you're not going to use it?
                      ... From the sample table above i Need the data that i mentioned before:
                      In Row PD02 should be counted if the PD is between 01 and 04 and INMPD is TRUE.
                      When you say "row" do you mean "column"?
                      When you say "PD" do you mean "priority"?
                      In Row PD05 should be counted if the PD is between 05 and 08 and INMPD is TRUE.
                      In Row PD12 should be counted if the PD is between 09 and 15 and INMPD is TRUE.
                      In Row TOTAL should be the total amount of Values for the DEPT.

                      The Total row should show the total ORDER's per DEPT.

                      I habe tried to modify the Query that i posted above to the COUNT statement that Frank postet, but i always get a single Group error.
                      I searched in this Forum and found out, it is not possible to put multiple case Statements in a COUNT function.
                      COUNT only takles 1 argument. If you want to count multiple things, use multiple COUNT expressions.
                      How can i solve that issue?
                      I'm still not sure what the issue is.
                      It looks like the extremely long query you posted is producing these results:
                      D PR      AGE_1      AGE_2 INMPD
                      - -- ---------- ---------- -----
                      A 05 18.0833333 8.83333333 FALSE
                      A 12 49.0531481 45.0582176 FALSE
                      S 02 49.0531481 45.0582176 FALSE
                      A 12 18.0833333 8.83333333 TRUE
                      Are you trying to pivot these results onto the results you just posted?
                      DEPT  PD02  PD05  PD12  TOTAL
                      ----  ----  ----  ----  -----
                      A        0     0     2      3
                      S        0     0     0      1
                      Why is the number in the row for dept='A' and the column pd12=2, and not 1?
                      • 8. Re: Query with conditional Counting Columns
                        993527
                        Hi Frank,

                        that One from you Look absolut more comfortable Instead of mine.
                        But i copied this ASCII table Out of my frontend Software Universal SQL.
                        How do you make your ASCII tables. All by Hand?
                        Okay this table is not really Big, but if you have a Big result table and you dont
                        Get your Results Out of a query, how do you make These tables?

                        Thanks
                        • 9. Re: Query with conditional Counting Columns
                          Frank Kulash
                          Hi,
                          Freakster235 wrote:
                          that One from you Look absolut more comfortable Instead of mine.
                          But i copied this ASCII table Out of my frontend Software Universal SQL.
                          How do you make your ASCII tables. All by Hand?
                          Sorry, I'm not sure I understand.
                          Are you asking how I made this?
                          DEPT  PD02  PD05  PD12  TOTAL
                          ----  ----  ----  ----  -----
                          A        0     0     2      3
                          S        0     0     0      1
                          I cut and pasted the results you posted into a text editor, and delete most of the characters. It's not that hard in my editor; I just hold doen the <delete> key.

                          I really need to know how you get the number 2 in your desired results. As I understand the rules, that should be 1, because only 1 row in you current result set has inmpd='TRUE'.

                          What is an "ASCII table"? How is it different from a regular table?
                          Okay this table is not really Big, but if you have a Big result table and you dont
                          Get your Results Out of a query, how do you make These tables?
                          Sorry, I don't understand this at all.
                          • 10. Re: Query with conditional Counting Columns
                            993527
                            Hi Frank,

                            ASCII Table means when you create a table with ASCII Characters like a dash instead of Lines like Excel or a similar Spreadsheet program.

                            If you have to Post a table which should be the result of a query and These result table have many columns and rows, and you are Not able to get the searched results out of your Database LIKE ME EVERYTIME :-)
                            How do you make These Not existing result table? All by Hand in a simple texteditor?
                            I Hope this describes ist more clear...

                            The Number 2 in the PD12 column Shows up while the Priority of two Orders from Dept 'A' is 12 and the Age_2 is below 30.

                            The Row PD02 should count each Job that Priority is between 1 and 3 and the age_2 is 5 or below
                            The Row PD05 should count each Job that Priority is between 4 and 8 and the age_2 is 8 or below
                            The Row PD12 should count each Job that Priority is between 9 and 15 and the age_2 is 30 or below

                            Thanks
                            • 11. Re: Query with conditional Counting Columns
                              Frank Kulash
                              Hi,
                              Freakster235 wrote:
                              Hi Frank,

                              ASCII Table means when you create a table with ASCII Characters like a dash instead of Lines like Excel or a similar Spreadsheet program.

                              If you have to Post a table which should be the result of a query and These result table have many columns and rows, and you are Not able to get the searched results out of your Database LIKE ME EVERYTIME :-)
                              How do you make These Not existing result table? All by Hand in a simple texteditor?
                              Yes. Use a text editor to show what results you want. It's easier than typing directly into this site.
                              If you have a query that produces some of the correct rows and/or columns, then you can paste those results into a text editor, and manually make changes.
                              I Hope this describes ist more clear...

                              The Number 2 in the PD12 column Shows up while the Priority of two Orders from Dept 'A' is 12 and the Age_2 is below 30.
                              Are we still talking about pivoting these results?
                              D PR      AGE_1      AGE_2 INMPD
                              - -- ---------- ---------- -----
                              A 05 18.0833333 8.83333333 FALSE
                              A 12 49.0531481 45.0582176 FALSE
                              S 02 49.0531481 45.0582176 FALSE
                              A 12 18.0833333 8.83333333 TRUE
                              There is only 1 row with dept='A', priority='12' and age_2<30. That's why I thought there should be a 1 in the output. Why do you want 2 instead?
                              • 12. Re: Query with conditional Counting Columns
                                993527
                                Hi Frank,

                                you totally right, there is only one order that me the criteria to be counted under PD12, sorry.
                                I looked only at the Age_1 not at the INMPD.
                                I make i really hard for you to help me, i don't do that with Intention ....

                                Thanks for your Patience with me
                                • 13. Re: Query with conditional Counting Columns
                                  Frank Kulash
                                  Hi,

                                  Here's one way:
                                  WITH     priorities          AS
                                  (
                                       SELECT      '01' AS min_priority, '03' AS max_priority, 5 AS max_age  FROM dual  UNION ALL
                                       SELECT      '04',                  '08',                  8                FROM dual  UNION ALL
                                       SELECT      '09',                 '15',                 30             FROM dual  
                                  )
                                  ,     got_aggregates          AS
                                  (
                                       SELECT    t1.dept
                                       ,       t1.priority
                                       ,       t1.create_date
                                       ,       MAX (CASE WHEN t2.wo_step = 'A' THEN t2.step_date END)     AS first_a_date
                                       ,       MAX (CASE WHEN t2.wo_step = 'U' THEN t2.step_date END)     AS last_u_date
                                       FROM       table_1     t1
                                       JOIN       table_2     t2  ON       t2.order_nr     = t1.order_nr
                                       GROUP BY  t1.dept
                                       ,       t1.priority
                                       ,       t1.create_date
                                  )
                                  SELECT    a.dept
                                  ,       COUNT (CASE WHEN p.min_priority = '01' THEN 1 END)     AS pd02
                                  ,       COUNT (CASE WHEN p.min_priority = '04' THEN 1 END)     AS pd05
                                  ,       COUNT (CASE WHEN p.min_priority = '09' THEN 1 END)     AS pd12
                                  ,       COUNT (*)                                           AS total
                                  FROM             got_aggregates  a
                                  LEFT OUTER JOIN      priorities      p  ON   a.priority          BETWEEN  p.min_priority
                                                                                            AND      p.max_priority
                                                          AND  NVL ( a.last_u_date
                                                                    , SYSDATE
                                                                ) - a.first_a_date <= p.max_age
                                  GROUP BY  a.dept
                                  ORDER BY  a.dept
                                  ;
                                  It seems like you have a fair amount of data connected with priority ranges; for example, there is a range that starts with '01', it ends with '03', and has an age threshold of 5 days. That sounds like data, and data belongs in tables, not hard-coded into CASE expressions. So I started by creating a sub-query (which behaves like a table) for that data. You might consider creating a real table for it, especially if the same priority groups play a role in other queries.

                                  Output:
                                  D       PD02       PD05       PD12      TOTAL
                                  - ---------- ---------- ---------- ----------
                                  A          0          0          1          3
                                  S          0          0          0          1
                                  • 14. Re: Query with conditional Counting Columns
                                    993527
                                    Hello Frank,

                                    your query Works, but it seems that i got missing results. I think i know what that Happen.
                                    I have a Couple of Orders that have the exactly same create Date, this occurs of the Orders will be
                                    Imported from file.

                                    And i think your 2nd subquery groups all Orders together when they have the same create Date and behause of that Group issue i have a Bunch of missing results.
                                    Is there a Way to modify this query? Die order_nr is my primary Key.
                                    I hope you know what i mean.

                                    Thanks.

                                    Reini
                                    1 2 Previous Next