This discussion is archived
13 Replies Latest reply: Oct 23, 2012 12:47 PM by 0racle RSS

Conditional Count !

0racle Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Try doing something like

    count(case when...

    or

    sum(case when...
  • 3. Re: Conditional Count !
    jeneesh Guru
    Currently Being Moderated
    Count(distinct case when prod_grade like '%+' then rep_id else null end)
  • 4. Re: Conditional Count !
    HuaMin Chen Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    Table DDL and INSERT statements added.
  • 8. Re: Conditional Count !
    rp0428 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Dear All,
    I will check and feedback ASAP.

    Thanks all.
  • 12. Re: Conditional Count !
    Sven W. Guru
    Currently Being Moderated
    >
    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 Newbie
    Currently Being Moderated
    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.

Legend

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