5 Replies Latest reply: Aug 1, 2013 7:22 PM by user13179060 RSS

    sql query help

    rajasekhar_n

      Hi,

      Please help me to write a query to display the data on the below format.

      Input :

      cuskey
      2001
      2001
      2002
      2002
      2003
      2004
      2001

       

      output format:

      cuskeytotaltotalsumvaltotalrowcount
      2001374
      2002274
      2003174
      2004174
        • 1. Re: sql query help
          Manik

          This way?

          ----------------

           

          WITH cuskey AS

                  (SELECT 2001 col FROM DUAL

                   UNION ALL

                   SELECT 2001 FROM DUAL

                   UNION ALL

                   SELECT 2002 FROM DUAL

                   UNION ALL

                   SELECT 2002 FROM DUAL

                   UNION ALL

                   SELECT 2003 FROM DUAL

                   UNION ALL

                   SELECT 2004 FROM DUAL

                   UNION ALL

                   SELECT 2001 FROM DUAL)

          SELECT col,

                 total,

                 SUM (total) OVER () totalsumval,

                 COUNT (*) OVER () totalrowcount

            FROM (SELECT DISTINCT col, COUNT (col) OVER (PARTITION BY col) total

                    FROM cuskey) order by col;

           

           

          COLTOTALTOTALSUMVALTOTALROWCOUNT
          2001374
          2002274
          2003174
          2004174

           

          Cheers,

          Manik,

          • 2. Re: sql query help
            rajasekhar_n

            Hi Manik,

             

            Thank you very much for quick reply,

             

            I need one more help from you, please help me to get below format.

            COLTOTALTOTALSUMVALTOTALROWCOUNT
            2001374
            2002273
            2003172
            2004171
            • 3. Re: sql query help
              Ashu_Neo

              Use row_number on same query(Written by Manik).

              SQL> with cuskey as
                2          (select 2001 col from dual
                3           union all
                4           select 2001 from dual
                5           union all
                6           select 2002 from dual
                7           union all
                8           select 2002 from dual
                9           union all
              10           select 2003 from dual
              11           union all
              12           select 2004 from dual
              13           union all
              14           select 2001 from dual)
              15  select col,
              16         total,
              17         sum (total) over () totalsumval,
              18          row_number() over(order by col desc) totalrowcount -- Edited
              19    from (select distinct col,
              20         count (col) over (partition by col) total
              21            from cuskey) order by col
              22  /

                     COL      TOTAL TOTALSUMVAL TOTALROWCOUNT
              ---------- ---------- ----------- -------------
                    2001          3           7             4
                    2002          2           7             3
                    2003          1           7             2
                    2004          1           7             1

              • 4. Re: sql query help
                rajasekhar_n

                Thanks a lot Ashu_Neo

                • 5. Re: sql query help
                  user13179060

                  WITH t AS

                       (SELECT 2001 KEY

                          FROM DUAL

                        UNION ALL

                        SELECT 2001

                          FROM DUAL

                        UNION ALL

                        SELECT 2002

                          FROM DUAL

                        UNION ALL

                        SELECT 2002

                          FROM DUAL

                        UNION ALL

                        SELECT 2003

                          FROM DUAL

                        UNION ALL

                        SELECT 2004

                          FROM DUAL

                        UNION ALL

                        SELECT 2001

                          FROM DUAL)

                  SELECT KEY col, SUM (l_count) OVER () totalsumval, l_count total,

                         CASE

                            WHEN l_count >= LEAD (l_count) OVER (ORDER BY l_count DESC)

                            AND LEAD (l_count) OVER (ORDER BY l_count DESC) <> 0

                               THEN l_count + 1

                            ELSE l_count

                         END ll

                    FROM (SELECT DISTINCT KEY,

                                          COUNT (KEY) OVER (PARTITION BY KEY ORDER BY KEY)

                                                                                        l_count

                                     FROM t);