10 Replies Latest reply: Feb 22, 2013 5:31 AM by 976439 RSS

    Count based on 2 criterias

    976439
      Hi, i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.
      select *
      count(case when queue is not null then 1 end) over(partition by group) as queue_orders
      from table_a;
      For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.

      If somebody could please advise.

      I have no data at the mo, just something that I am trying to start off.
        • 1. Re: Count based on 2 criterias
          sb92075
          973436 wrote:
          Hi, i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.
          select *
          count(case when queue is not null then 1 end) over(partition by group) as queue_orders
          from table_a;
          For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.

          If somebody could please advise.

          I have no data at the mo, just something that I am trying to start off.
          SELECT COUNT(*) FROM TABLE_A
          WHERE QUEUE IS NOT NULL
          AND GROUP = 1;

          Handle:     973436
          Status Level:     Newbie
          Registered:     Nov 27, 2012
          Total Posts:     63
          Total Questions:     22 (17 unresolved)


          why so MANY unanswered questions?
          • 2. Re: Count based on 2 criterias
            Solomon Yakobson
            sb92075 wrote:

            SELECT COUNT(*) FROM TABLE_A
            WHERE QUEUE IS NOT NULL
            AND GROUP = 1;
            Why not:
            SELECT COUNT(QUEUE) FROM TABLE_A
            WHERE GROUP = 1;
            SY.
            • 3. Re: Count based on 2 criterias
              Frank Kulash
              Hi,
              973436 wrote:
              Hi, i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.

              select *
              count(case when queue is not null then 1 end) over(partition by group) as queue_orders
              from table_a;
              GROUP is an Oracle reserved word, so it's not a very good column name. Why not use grp or something like order_group instead?
              If there is anything besides * in the SELECT clause, then the * has to be qualified with the table name or alias.
              "COUNT (...)" is a new column. You need a comma before COUNT to delimit it from the earlier columns in the SELECT clause.

              Aside from that, the code above looks like it would work. (I have no way of knowing wheteher it works the way you want or not. Post CREATE TABLE and INSERT statements for some sample data, and the results you want from that data.)
              However, a simpler way to get the same results would be:
              select  table_a.*
              ,       count (queue) over(partition by grp) as queue_orders
              from    table_a;
              For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.
              If the code above doesn't do what you want, then I don't understand what you want.
              If somebody could please advise.

              I have no data at the mo, just something that I am trying to start off.
              Start off by thinking of some sample data that shows what you want to do, and then post CREATE TABLE and INSERT statements for that data, and the results you want from that data.
              See the forum FAQ {message:id=9360002}
              • 4. Re: Count based on 2 criterias
                Nicosa-Oracle
                Hi,

                Count() does it natively :
                with t(id,grp,queue) as (
                select 1, 1, 'a' from dual
                union all select 2, 1, 'b' from dual
                union all select 3, 1, null from dual
                union all select 4, 2, 'x' from dual
                union all select 5, 2, null from dual
                )
                select id, grp, queue, count(queue) over (partition by grp) qorder
                from t;
                
                [11.2] Scott @ My11g > @t
                
                        ID        GRP QUEUE     QORDER
                ---------- ---------- ----- ----------
                         1          1 a              2
                         2          1 b              2
                         3          1                2
                         4          2 x              1
                         5          2                1
                • 5. Re: Count based on 2 criterias
                  976439
                  Thanks guys, always trying to get ahead of myself, hence when I get stuck ask a question without realizing that people can't read my brains! So apologies once again. I have managed to get hold of the data so will post create and insert statements and also the query I am trying to write.

                  Also going to go through my posts as really need to close the answered ones off.
                  • 6. Re: Count based on 2 criterias
                    976439
                    ok as promised here is my create and insert statements.
                    CREATE TABLE QUEUE_DATA 
                       (     
                        ORDER_NUM VARCHAR2(255 BYTE), 
                         SEARCH_TIME DATE, 
                         PRODUCT VARCHAR2(255 BYTE), 
                         CP VARCHAR2(255 BYTE),  
                         VQ VARCHAR2(255 BYTE), 
                         DELIV_DT DATE
                       );
                    My insert statements:
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761574726',to_date('22-JAN-13 10:11:00','DD-MON-RR HH24:MI:SS'),'WL','SYSTEMS LTD',null,null);
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761745824',to_date('22-JAN-13 01:34:00','DD-MON-RR HH24:MI:SS'),'LU','ASSETS LIMITED',null,null);
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761745824',to_date('23-JAN-13 17:06:00','DD-MON-RR HH24:MI:SS'),'LU','ASSETS LIMITED',null,null);
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761745824',to_date('29-JAN-13 15:00:35','DD-MON-RR HH24:MI:SS'),'LU','ASSETS LIMITED',null,null);
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761745824',to_date('24-JAN-13 21:22:00','DD-MON-RR HH24:MI:SS'),'LU','ASSETS LIMITED','VQ_LIUT_V04',to_date('24-JAN-13 21:13:45','DD-MON-RR HH24:MI:SS'));
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16761745824',to_date('29-JAN-13 12:35:08','DD-MON-RR HH24:MI:SS'),'LU','ASSETS LIMITED',null,null);
                    Insert into QUEUE_DATA (ORDER_NUM,SEARCH_TIME,PRODUCT,CP,VQ,DELIV_DT) values ('1-16762406337',to_date('07-DEC-12 18:15:00','DD-MON-RR HH24:MI:SS'),'LU','WHOLESALE',null,null);
                    • 7. Re: Count based on 2 criterias
                      Frank Kulash
                      Hi,
                      973436 wrote:
                      ok as promised here is my create and insert statements.
                      Thanks.
                      Don;t forget to post the results you want from that data, and an explanation of how you get those results from that data.

                      Are thesed the results you want?
                      `                                                                        QUEUE_
                      ORDER_NUM     SEARCH_TIME PRODUCT CP             VQ          DELIV_DT    ORDERS
                      ------------- ----------- ------- -------------- ----------- ----------- ------
                      1-16761745824 29-Jan-2013 LU      ASSETS LIMITED                              1
                      1-16761745824 22-Jan-2013 LU      ASSETS LIMITED                              1
                      1-16761745824 23-Jan-2013 LU      ASSETS LIMITED                              1
                      1-16762406337 07-Dec-2012 LU      WHOLESALE                                   1
                      1-16761745824 24-Jan-2013 LU      ASSETS LIMITED VQ_LIUT_V04 24-Jan-2013      1
                      1-16761745824 29-Jan-2013 LU      ASSETS LIMITED                              1
                      1-16761574726 22-Jan-2013 WL      SYSTEMS LTD                                 0
                      Here's one way to get them:
                      SELECT  queue_data.*
                      ,       COUNT (vq) OVER (PARTITION BY product) as queue_orders
                      FROM    queue_data;
                      Are all the columns in the table really significant in this problem?
                      • 8. Re: Count based on 2 criterias
                        976439
                        Frank thanks for that. That is exactly what I am looking to do. I have added a bit more on my resultset which is shown below.
                        ` ORDER_ VQ_
                        ORDER_NUM SEARCH_TIME PRODUCT CP VQ DELIV_DT NUM_CO COUNT COUNT_1
                        ------------- ----------- ------- -------------- ----------- ----------- ------ ------ -------
                        1-16761745824 29-Jan-2013 LU ASSETS LIMITED 5 1 0
                        1-16761745824 22-Jan-2013 LU ASSETS LIMITED 5 1 0
                        1-16761745824 23-Jan-2013 LU ASSETS LIMITED 5 1 0
                        1-16762406337 07-Dec-2012 LU WHOLESALE 1 0 1
                        1-16761745824 24-Jan-2013 LU ASSETS LIMITED VQ_LIUT_V04 24-Jan-2013 5 1 0
                        1-16761745824 29-Jan-2013 LU ASSETS LIMITED 5 1 0
                        1-16761574726 22-Jan-2013 WL SYSTEMS LTD 1 0 1

                        1. Need to count order numbers that belong to each CP so i.e. 1-16761745824 would have a count of 5 for Assets Ltd. I have attempted the write the code for this..
                        COUNT(*) OVER (PARTITION BY order_num) as order_num_co
                        2. Need to count all non null values in VQ column for each CP i.e. only 1 value showing in VQ that belongs to Assets Ltd so vq_count should be 1. Code for this is the following as you suggested.
                        COUNT(vq) OVER (PARTITION BY cp) as voice_count
                        3. Lastly I would like to count all the non null values in VQ that have an order_num_co of 1 only and this is where I am stuck as not sure how to write this.

                        Thanks btw for all your help really appreciate it.

                        Edited by: 973436 on 15-Feb-2013 01:45
                        • 9. Re: Count based on 2 criterias
                          Frank Kulash
                          Hi,
                          973436 wrote:
                          Frank thanks for that. That is exactly what I am looking to do. I have added a bit more on my resultset which is shown below.
                          ` ORDER_ VQ_
                          ORDER_NUM SEARCH_TIME PRODUCT CP VQ DELIV_DT NUM_CO COUNT COUNT_1
                          ------------- ----------- ------- -------------- ----------- ----------- ------ ------ -------
                          1-16761745824 29-Jan-2013 LU ASSETS LIMITED 5 1 0
                          1-16761745824 22-Jan-2013 LU ASSETS LIMITED 5 1 0
                          1-16761745824 23-Jan-2013 LU ASSETS LIMITED 5 1 0
                          1-16762406337 07-Dec-2012 LU WHOLESALE 1 0 1
                          1-16761745824 24-Jan-2013 LU ASSETS LIMITED VQ_LIUT_V04 24-Jan-2013 5 1 0
                          1-16761745824 29-Jan-2013 LU ASSETS LIMITED 5 1 0
                          1-16761574726 22-Jan-2013 WL SYSTEMS LTD 1 0 1

                          1. Need to count order numbers that belong to each CP so i.e. 1-16761745824 would have a count of 5 for Assets Ltd. I have attempted the write the code for this..
                          COUNT(*) OVER (PARTITION BY order_num) as order_num_co
                          COUNT (*) does not count order_nums; it counts rows.  If every row has an order_num, then the counts will be the same. If the explanation of how you get the results includes cp, then I would expect the code that produces it include cp, too.  Maybe you meant:
                          COUNT (*) OVER (PARTITION BY cp) AS order_num_count
                          In this sample data, rows have the same order_num if and only if they have the same cp, so the results happen to be the same.
                          >
                          
                          2. Need to count all non null values in VQ column for each CP i.e. only 1 value showing in VQ that belongs to Assets Ltd so vq_count should be 1. Code for this is the following as you suggested.
                          COUNT(vq) OVER (PARTITION BY cp) as voice_count
                          3. Lastly I would like to count all the non null values in VQ that have an order_num_co of 1 only and this is where I am stuck as not sure how to write this.
                          Sorry, I don't understand what you want.  For example, you say you want to count non-NULL values of vq, yet the only row in this sample data that has a non-NULL vq has a 0 in the count_1 column.  On the other hand, count_1=1 on these rows
                          1-16762406337 07-Dec-2012 LU WHOLESALE 1 0 1
                          ...
                          1-16761574726 22-Jan-2013 WL SYSTEMS LTD 1 0 1
                          that have nothing in common with the row where vq is not NULL.
                          Explain, step by step, how you get the values you posted.  Also explain how the computation is partitioned; that is, will all the rows with the same cp have the same value for this column?
                          
                          Having such long rows in the result set doesn't make it any easier to understand.  Do you really need to display all these columns just to see how to compute these new columns?  Do the strings have to be so long in the sample data?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                          • 10. Re: Count based on 2 criterias
                            976439
                            Frank, apologies for the delay in responding, as been away. Unfortunately the dataset I am receiving is being reviewed as doesnt seem right as alot of data is missing. Appreciate your time and support, really helping to me understand things alot better.