13 Replies Latest reply: Oct 23, 2012 2:47 PM by 0racle RSS

    Conditional Count !

    0racle
      Hi All,

      We have an issue that requires to select the counts of Representatives from the Representatives table per each Branch:

      Representatives Table structure :
      CREATE TABLE Representatives(
      No NUMBER (10), -- Branch Number 
      Rep_ID NUMBER(20) , -- The Representative ID
      Pro_ID NUMBER(20) , -- The product ID
      Pro_Grade VARCHAR2(10)); -- The product grades. Available values (A+, A , B+, B, C+, C, D, E, F, G).
      Sample Data:
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (1,500,622,'A');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (1,500,333,'B+');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (1,501,362,'C');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (1,501,654,'A');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (2,502,692,'B');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (2,502,234,'D');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (2,502,698,'B+');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (2,503,111,'A');
      Insert into  REPRESENTATIVES (NO,REP_ID,PRO_ID,PRO_GRADE) values (2,503,236,'C');
      We need to count the Representatives who didn't sell any of the ( + ) products category at all (A+, B+ .. ect) per each branch (Grouped by No).
      Which means that if the Representative sold only one ( + ) product he shouldn't be counted.

      Thank you.

      Edited by: 0racle on Oct 8, 2012 3:09 AM -- Added Table DDL and INSERT statements.
        • 1. Re: Conditional Count !
          sb92075
          0racle wrote:
          Hi All,

          We have an issue that requires to select the counts of Representatives from the Representatives table per each Branch:

          Representatives Table structure :
          Col No -- Branch Number
          Col Rep_ID -- the Representative ID
          Col Pro_ID -- the product ID
          Col Pro_Grade -- the product grades. Available values (A+, A , B+, B, C+, C, D, E, F, G)

          We need to count the Representatives who didn't sell any of the ( + ) products category at all (A+, B+ .. ect) per each branch (Grouped by No).
          Which means that if the Representative sold only one ( + ) product he shouldn't be counted.
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Conditional Count !
            SomeoneElse
            Try doing something like

            count(case when...

            or

            sum(case when...
            • 3. Re: Conditional Count !
              jeneesh
              Count(distinct case when prod_grade like '%+' then rep_id else null end)
              • 4. Re: Conditional Count !
                HuaMin Chen
                Try
                select No, count(distinct case when pro_grade like '%+%' then null else Rep_id end)
                from Representatives
                group by No;

                Edited by: HuaMin Chen on Oct 7, 2012 10:25 PM
                • 5. Re: Conditional Count !
                  0racle
                  Dear All,

                  All the mentioned solutions will not provide the correct count because the logic you have been used will always count the representative if he has sold any ( + ) grade product along with a regular product.
                  Please refer to the below sample of data :
                  No------Rep_ID-------Pro_ID--------Pro_Grade
                  |1|------|500|--------|622|----------| A
                  |1|------|500|--------|333|----------| B+ 
                  |1|------|501|--------|362|----------| C 
                  |1|------|501|--------|654|----------| A 
                  |2|------|502|--------|692|----------| B 
                  |2|------|502|--------|234|----------| D 
                  |2|------|502|--------|698|----------| B+ 
                  |2|------|503|--------|111|----------| A 
                  |2|------|503|--------|236|----------| C 
                  You notice that the Representative *500* will be counted even if he sold a B+ product also the Representative *502* .
                  Please provide a new one.
                  Thanks.
                  • 6. Re: Conditional Count !
                    rp0428
                    >
                    We have an issue that requires to select the counts of Representatives from the Representatives table per each Branch:

                    Representatives Table structure :
                    Col No -- Branch Number
                    Col Rep_ID -- the Representative ID
                    Col Pro_ID -- the product ID
                    Col Pro_Grade -- the product grades. Available values (A+, A , B+, B, C+, C, D, E, F, G)

                    We need to count the Representatives who didn't sell any of the ( + ) products category at all (A+, B+ .. ect) per each branch (Grouped by No).
                    Which means that if the Representative sold only one ( + ) product he shouldn't be counted.
                    >
                    Updated after DDL and INSERT statements were provided
                    with bad_reps as (select distinct rep_id from representatives
                       where pro_grade like '%+%')
                    select no, count(distinct rep_id) 
                    from Representatives
                    where rep_id not in (select rep_id from bad_reps)
                    group by no
                    
                    NO,COUNT(DISTINCTREP_ID)
                    1,1
                    2,1
                    • 7. Re: Conditional Count !
                      0racle
                      Table DDL and INSERT statements added.
                      • 8. Re: Conditional Count !
                        rp0428
                        Thanks - I updated my query above.

                        Seems like it would be more useful to know WHO the reps were though
                        with bad_reps as (select distinct rep_id from representatives
                           where pro_grade like '%+%')
                        select distinct no, rep_id 
                        from Representatives
                        where rep_id not in (select rep_id from bad_reps)
                        order by no, rep_id
                        
                        NO,REP_ID
                        1,501
                        2,503
                        • 9. Re: Conditional Count !
                          HuaMin Chen
                          0racle wrote:
                          Dear All,

                          All the mentioned solutions will not provide the correct count because the logic you have been used will always count the representative if he has sold a ( + ) grade product along with a regular product.
                          Please refer to the below sample of data :

                          No---Rep_ID----Pro_ID---Pro_Grade
                          |1|------|500|--------|622|----------| A
                          |1|------|500|--------|333|----------| B+
                          |1|------|501|--------|362|----------| C
                          |1|------|501|--------|654|----------| A
                          |2|------|502|--------|692|----------| B
                          |2|------|502|--------|234|----------| D
                          |2|------|502|--------|698|----------| B+
                          |2|------|503|--------|111|----------| A
                          |2|------|503|--------|236|----------| C

                          You notice that the Representative *500* will be counted even if he sold a B+ product also the Representative *502* .
                          Please provide a new one.
                          Thanks.
                          Try
                          select No, count(distinct case when pro_grade like '%+%' then null else Rep_id end)
                          from Representatives
                          where No not in(select No
                          from Representatives
                          where pro_grade like '%+%')
                          group by No;
                          • 10. Re: Conditional Count !
                            Frank Kulash
                            Hi,

                            Thanks for posting the CREATE TABLE and INSERT statements.
                            Don't forget to post the results you want from that sample data. Describing the results is great, but describe them in addition to, no instead of, actually posting them.

                            Are these the results you want?
                            `   REP_ID
                            ----------
                                   501
                                   503
                            Here's one way to get them:
                            SELECT       rep_id
                            FROM       representatives
                            GROUP BY  rep_id
                            --,       no      -- ?
                            HAVING       COUNT ( CASE
                                              WHEN  pro_grade  LIKE '%+%'
                                            THEN  1
                                          END           
                                        ) = 0
                            ;
                            0racle wrote:We need to count the Representatives who didn't sell any of the ( + ) products category at all (A+, B+ .. ect) per each branch (Grouped by No).
                            Does the column called no play some role in this problem> If so, what? Maybe you need to add no to the GROUP BY clause, above
                            • 11. Re: Conditional Count !
                              0racle
                              Dear All,
                              I will check and feedback ASAP.

                              Thanks all.
                              • 12. Re: Conditional Count !
                                Sven W.
                                >
                                Aim
                                select the counts of Representatives from the Representatives table per each Branch:
                                We need to count the Representatives who didn't sell any of the ( + ) products category at all (A+, B+ .. ect) per each branch (Grouped by No).
                                Which means that if the Representative sold only one ( + ) product he shouldn't be counted.
                                Thanks for providing a business description.

                                A solution could look like this.

                                First count all representatives, then substract the number of representatives that sold + grade products.
                                select no as branch
                                        ,count(distinct rep_id) 
                                         -
                                         count(distinct case when PRO_GRADE in ('A+','B+','C+') then rep_id end) 
                                         as "total non+ reps"
                                from representatives
                                group by no
                                • 13. Re: Conditional Count !
                                  0racle
                                  Thank you all for your co-operation.

                                  Dear rp0428,
                                  the queries you provided me extracted the required outputs, but for some reasons it seems that it has a performance issue, even I believe that using that technique provides a good performance.
                                  may be because we work under a roll-based database mode, actually I don't know why!.. if you have any explanation please provide me with.
                                  I have flagged both of your queries as helpful.

                                  Dear Frank Kulash,
                                  Your query is the one I have used, and it works fine.

                                  Thanks again.