11 Replies Latest reply: Feb 6, 2013 9:55 PM by SRN RSS

    2 different counts in one querry

    Chloe_19
      I am trying to get a distinct count of ID who only ever had HECS as a payment and IDs who moved into it. So they had other payment mehtods such as cash or card and then HECS.

      So below ID 1 has payment as cash card and HECS so (MOVED)
      ID 2 has only had HECS so straight HECS
      ID 3 has had cash and HECS so (MOVED)
      ID 4 has HECS only so HECS
      CREATE TABLE DAN_GR_DELETE
      (ID    VARCHAR2(8),
      PAYMENT    VARCHAR2(8),
      ITEM VARCHAR2(8)) 
      
      
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'CREDIT','CAR');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'CASH','BUS');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'HECS','BIKE');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (2,'HECS','BIKE');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (2,'HECS','CAR');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (3,'CASH','BUS');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (3,'HECS','CAR');
      INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (4,'HECS','CAR');
      ID     PAYMENT     ITEM
      1     CREDIT     CAR
      1     CASH      BUS 
      1     HECS     BIKE
      2     HECS     BIKE
      2     HECS     CAR
      3     CASH      BUS 
      3     HECS     CAR
      4     HECS     CAR
      WANT:
      MOVED     HECS
      2     2
      using:
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
        • 1. Re: 2 different counts in one querry
          rp0428
          Why isn't ID 4 in your result list? They only had HECS.

          Or did you include them in the HECS only list?
          • 2. Re: 2 different counts in one querry
            Chloe_19
            I Included ID 4 in the results

            So 1 and 3 have move to hecs
            and 2 and 4 only ever had hecs so stayed HECS

            so result is 2 in MOVED
            and 2 in HECS
            • 3. Re: 2 different counts in one querry
              ---Brodyaga---
              Hello.
              with tt as (SELECT d.id, count(distinct payment) OVER (partition by id) cnt
                          FROM DAN_GR_DELETE d) 
              SELECT (select count(distinct id)
                      from tt
                      where cnt = 1) HECS,
                     (select count(distinct id)
                      from tt
                      where cnt != 1) MOVED
              FROM DUAL
              Edited by: 985181 on Feb 5, 2013 2:11 AM
              • 4. Re: 2 different counts in one querry
                Chloe_19
                Hi
                Thank you 985181

                But that gives 3 HECS and 1 MOVED
                • 5. Re: 2 different counts in one querry
                  ---Brodyaga---
                  Ok. May be...
                  with tt as (SELECT distinct d.id, count(distinct payment) OVER (partition by id) cnt
                              FROM DAN_GR_DELETE d) 
                  SELECT (select count(0)
                          from tt
                          where cnt = 1) HECS,
                         (select count(0)
                          from tt
                          where cnt != 1) MOVED
                  FROM DUAL
                  HECS                    MOVED                   
                  ----------------------- ----------------------- 
                  2                       2
                  • 6. Re: 2 different counts in one querry
                    Chloe_19
                    Thank you.
                    You were right the first time ;)
                    • 7. Re: 2 different counts in one querry
                      jeneesh
                      Or simply,
                      select sum(c_cnt) moved,
                             sum(h_cnt-c_cnt) hecs
                      from
                      (
                          select id,
                                 max(decode(payment,'CASH',1,0)) c_cnt,
                                 max(decode(payment,'HECS',1,0)) h_cnt
                          from dan_gr_delete
                          group by id
                      );
                      
                      MOVED HECS
                      ----- ----
                          2    2 
                      • 8. Re: 2 different counts in one querry
                        SRN
                        good solutions.. but, these are applicable to the above given data only..
                        would it be possible to have a query which works with all possible data scenarios?

                        Thanks,
                        Sandeep
                        • 9. Re: 2 different counts in one querry
                          jeneesh
                          SRN wrote:
                          good solutions.. but, these are applicable to the above given data only..
                          Query should be written according to the requirement and the data you expect in your table..
                          would it be possible to have a query which works with all possible data scenarios?
                          What are the possible scenarios?

                          And better, post a new thread if you have your question than following up an answered thread..
                          • 10. Re: 2 different counts in one querry
                            SRN
                            related to same thread itself..
                            was trying to find a query, in case data changes as below:
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'CREDIT','CAR');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'CREDIT','BUS');      ---changed CASH to CREDIT
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (1,'HECS','BIKE');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (2,'HECS','BIKE');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (2,'HECS','CAR');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (3,'CASH','BUS');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (3,'HECS','CAR');
                            INSERT INTO DAN_GR_DELETE (ID,PAYMENT,ITEM) VALUES (4,'HECS','CAR');
                            here output deviates from the expected thread logic of MOVED and HECS count..
                            • 11. Re: 2 different counts in one querry
                              SRN
                              below query will do..
                              select 
                                   count(case RESULT when 0 then 1 end) HECS,
                                   count(case RESULT when 1 then 1 end) MOVED
                              from
                              (
                                select
                                 case 
                                       when c_cnt=1 or cr_cnt=1 then 1
                                       else 0 
                                 END RESULT
                                from
                                (     
                                  select id,
                                         max(decode(payment,'CASH',1,0)) c_cnt,
                                         max(decode(payment,'HECS',1,0)) h_cnt,
                                         max(decode(payment,'CREDIT',1,0)) cr_cnt
                                  from dan_gr_delete
                                  group by id
                                )
                              );