2 Replies Latest reply on Dec 17, 2015 5:02 PM by rp0428

    How to display count of value which has no value.

    TanmoyMoulik

      Hi Team,

      We have requirement where we want to display the count of value for whihc there is no corresding value.

       

      FOr ex

      I have table

       

      TTable structrue-T1

       

      Priority    TT

      1             IM1

      1             IM2

      3            IM3

       

       

      Now I am writting querry

       

      select count(TT),priority from table T1 group by priority

       

      It display value as

       

      2  1

      1   3

       

      However I want to display value in this format:

       

      2   1

      0   2

      1   3

       

      So also I want to display the count of priority 2 though it has no value.

      Can any one help me how to get this value.

       

      Thannks,

      Tanmoy

        • 1. Re: How to display count of value which has no value.
          renssak

          If there are not too many priotities, you can use this one:

           

          I "create" a temporyry table wih all priorities.

           

          select COUNT(TT.TT),

          FILLER.PRIORITY

          from TTABLE  TT,

              (SELECT

                CASE

                  WHEN rownum = 1

                  THEN 1

                  WHEN rownum = 2

                  THEN 2

                  WHEN rownum = 3

                  THEN 3

                END AS PRIORITY

              FROM all_objects

              WHERE rownum <=3

              ) FILLER

          WHERE FILLER.PRIORITY = TT.PRIORITY ( + )

          GROUP BY FILLER.PRIORITY

           

          COUNT(TT.TT) PRIORITY

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

                     2        1

                     0        2

                     1        3

           

          Or you create a real table with only one column with all priorities.

          • 2. Re: How to display count of value which has no value.

            Wrong forum!

             

            This forum is ONLY for Sql Developer questions/issues.

             

            Please mark the thread ANSWERED and repost your question in the SQL and PL/SQL forum.

            SQL & PL/SQL

            However I want to display value in this format:

             

            2   1

            0   2

            1   3

             

            So also I want to display the count of priority 2 though it has no value.

            Can any one help me how to get this value.

            Why? Why would you want to display the WRONG results?

             

            There are no rows with priority 7 so that should be in the result also 0 - 7.

             

            And there are no 6s, 8s, 23s, 874s, etc - there are an INFINITE number of possible priorities for which there is no value. Shouldn't they all be included? There is NOTHING to indicate that '2' is the ONLY priority that COULD BE possible.

             

            Post in the correct forum and provide ALL of the info needed to help - please do NOT follow up here.