6 Replies Latest reply: Feb 10, 2013 10:10 AM by Stew Ashton RSS

    Comparing two counts using subquery

    990190
      Hi all,

      Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
      Eg: R(x,y)
      Select count(x)
      from R
      group by x;

      Then I want to run another query to determine which x's have the same count value and output these corresponding x's.

      Thanks!

      //Mods: Sorry have moved this here. Please delete other thread as on reflection obviously in wrong section. Thanks
        • 1. Re: Comparing two counts using subquery
          Frank Kulash
          Hi,
          987187 wrote:
          Hi all,

          Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
          Eg: R(x,y)
          Select count(x)
          from R
          group by x;

          Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
          It sounds like you want:
          SELECT  CASE
                      WHEN  COUNT (x) = COUNT (y)
                      THEN  'Same'
                      ELSE  'Different'
                  END     AS x_vs_y
          FROM    your_table;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Comparing two counts using subquery
            990190
            Hi there, thanks for that.

            I should have written that I need it in subquery form as that is what is required (ie more complicated that it needs to be). I'm guessing that it can be done simply using group, having, where, in, exists or some derivative of but I can't figure it out for the life of me!
            • 3. Re: Comparing two counts using subquery
              jeneesh
              987187 wrote:
              Hi there, thanks for that.

              I should have written that I need it in subquery form as that is what is required (ie more complicated that it needs to be). I'm guessing that it can be done simply using group, having, where, in, exists or some derivative of but I can't figure it out for the life of me!
              Please provide sample data (CREATE TABLE and INSERT statements) and expected output..
              • 4. Re: Comparing two counts using subquery
                Stew Ashton
                987187 wrote:
                I essentially want to write a query to determine when two counts are the same using a subquery.
                Eg: R(x,y)
                Select count(x)
                from R
                group by x;

                Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
                It's important for you and us to share the same understanding of your requirement. Let me try to restate:

                - First, for each value of X that is not null you want the number of rows where that value occurs.
                (If you want to include the rows where X is null, you need to say count(*) and not count(X) )

                - Second, you want to group the X values together by count.

                So, you need to aggregate twice, once on X to get the count and once on the count to get all the X's with the same count.

                When you aggregate the X's on the count, you need an aggregation function. There is one available in 11GR2 called LISTAGG.
                I'm going to assume you have 11GR2 because you did not mention your Oracle version. The next time you ask a question, be sure to indicate your precise Oracle version.
                DROP TABLE T;
                create table t as
                WITH DATA AS (
                  SELECT LEVEL X FROM DUAL CONNECT BY LEVEL <= 10
                )
                SELECT A.X FROM DATA A, DATA B;
                
                SELECT CNT,
                LISTAGG(X, ',') WITHIN GROUP(ORDER BY X) LIST_X
                from (
                  SELECT X, COUNT(X) CNT FROM T
                  GROUP BY X
                )
                group by cnt;
                
                CNT LIST_X                                                                                                                                       
                --- ------------------------- 
                 10 1,2,3,4,5,6,7,8,9,10
                • 5. Re: Comparing two counts using subquery
                  Solomon Yakobson
                  Since you didn't provide data sample I'll use emp table excluding JAMES so departments 20 & 30 get same number of employees. Then:
                  with t as (
                             select  deptno,
                                     count(*) cnt,
                                     count(*) over(partition by count(*)) cnt_cnt
                               from  emp
                               where ename != 'JAMES'
                               group by deptno
                            )
                  select  deptno,
                          cnt
                    from  t
                    where cnt_cnt > 1
                  /
                  
                      DEPTNO        CNT
                  ---------- ----------
                          30          5
                          20          5
                  
                  SQL>
                  SY.
                  • 6. Re: Comparing two counts using subquery
                    Stew Ashton
                    Solomon,

                    My solution doesn't filter out "singleton" counts, so you have me there.

                    Also, your use of nested aggregation in analytics is brilliant. First time I've seen it.

                    Regards, Stew