1 2 3 Previous Next 44 Replies Latest reply: Jun 25, 2014 5:38 AM by Lothar Flatz Go to original post RSS
      • 30. Re: How to divide a row in a table
        BluShadow

        As already mentioned above... how is the query suppose to know which hobbynr's are missing from the result.

        What if there were no people with hobbynr 5?  How would it know to show number 5 with 0 people if there's not other table supplying that relationship?

         

        The query you've posted is doing a count of personnr, yet you STILL haven't bothered (are you just lazy?) to post some example source data

         

        No example data = no solution.  YOUR CHOICE!

        • 31. Re: How to divide a row in a table
          user4585215

          Hi,

           

          Hope, whatever values you require in hobbynr must be present in table hobbytab (without putting filter of year). If i correctly understood this puzzle then below query will give the desired result. Just modified your query to make outer with your data with distinct values in hobbytab table.

           

          select a.hobby as hobbynr, b.personnr

            from (select distinct hobbynr hobby from hobbytab) a,

                 (select h.hobbynr, count(h.personnr) personnr

                    from hobbytab h

                   where h.year = 2014

                   group by h.hobbynr

                   order by h.hobbynr) b

          where a.hobby = b.hobbynr(+)

          • 32. Re: How to divide a row in a table
            KayK

            Hi,

            you can't achieve what you want in a serious manner.


            You can produce your output with something like this

            with hobbytab (year, hobbynr, personnr) as

                       (  select 2014, 2, 1 from dual union all

                          select 2014, 2, 2 from dual union all

                          select 2014, 4, 2 from dual union all

                          select 2014, 4, 5 from dual union all

                          select 2014, 4, 6 from dual union all

                          select 2014, 5, 1 from dual

                 )

            select u.unique_hobbies,

                   count(h.personnr)

              from hobbytab h,

                   (select level as unique_hobbies from dual connect by level <= 5) u

            where h.year (+)= 2014

               and u.unique_hobbies = h.hobbynr(+)

            group by u.unique_hobbies

            order by u.unique_hobbies;

             

            But this is not a solution ... it's only a workaround to produce this given output. And you don't now what happens if your data or the real world is changing in the future.
            You have to change your data model, you need a table only with the available hobbies.

            regards
            Kay

            • 33. Re: How to divide a row in a table
              acd6a2be-efda-4b11-b98e-80ece826ad23

              i didnt know how cleary enough i can post my problem

               

              i will do it on a other way.

               

              Table: Hobbytab

               

              Columns:

              SEQ_ID, Jahr_ID, KTO_ID, Player_NR, Hobby_NR .....

              577269      2008     130333         8                  34

              577270     2005      146122       133                5

              577271   2006    146122     135             6
              5772722005 146122         19              5

               

              My Select

              Select distinct h.hobby_nr

              from hobbytab h

              where h.jahr_id = 2005

              Output

               

              Hobby_nr

              1

              3

              5

               

              But I WANT

               

              Hobby_nr

              1

              2

              3

              4

              5

              • 34. Re: How to divide a row in a table
                user4585215

                Hi,

                 

                As i can understand your data orgonisation, i will simply suggest the below query(All hobbies you want display are there in hobbytab table without putting any filter on year):

                select a.hbnr hobbynr,      

                       b.cnt_personnr personnr

                  from (select distinct hobbynr as hbnr from hobbytab) a      

                       (select h.hobbynr, count(h.personnr) cnt_personnr       

                          from hobbytab h       

                         where h.year = 2014       

                         group by h.hobbynr       

                         order by h.hobbynr)

                where a.hbnr = b.hobbynr(+)

                • 35. Re: How to divide a row in a table
                  BluShadow

                  I gave an example of providing example data using a WITH clause back on post #15.  Is it too hard to do the same so we can copy paste it to our own database?

                   

                  You've now posted some example data (in a format we can't use), yet your expected output shows hobby_nr's 1,2,3,4,5.  What about the numbers 6 and 34 from the example data; why aren't they in the expected output?

                   

                  You need to provide example data, with expected output from that example data.

                  • 36. Re: How to divide a row in a table
                    BluShadow

                    I suspect you're after something like this...

                     

                     

                    SQL> ed
                    Wrote file afiedt.buf

                      1  with t (hobbynr,playernr) as (
                      2          select 1, 1 from dual union all
                      3          select 2, 1 from dual union all
                      4          select 1, 2 from dual union all
                      5          select 2, 2 from dual union all
                      6          select 2, 3 from dual union all
                      7          select 5, 4 from dual union all
                      8          select 3, 4 from dual union all
                      9          select 3, 3 from dual union all
                    10          select 5, 3 from dual union all
                    11          select 1, 3 from dual union all
                    12          select 1, 5 from dual union all
                    13          select 5, 5 from dual union all
                    14          select 3, 6 from dual union all
                    15          select 5, 6 from dual
                    16         )
                    17  --
                    18  -- end of test data
                    19  --
                    20  select x.hobbynr, count(t.playernr) as players
                    21  from   (select minh+level-1 as hobbynr
                    22          from   (select min(hobbynr) as minh, max(hobbynr) as maxh from t)
                    23          connect by level <= (maxh-minh)+1
                    24         ) x
                    25         left outer join t on (x.hobbynr = t.hobbynr)
                    26  group by x.hobbynr
                    27* order by 1
                    SQL> /

                       HOBBYNR    PLAYERS
                    ---------- ----------
                             1          4
                             2          3
                             3          3
                             4          0
                             5          4

                     

                     

                    But, this still doesn't resolve the issue that if there's a hobby number of 6 that also hasn't got any players, then it's not going to come out with a count of 0 because the information about what hobby's are available is not being obtained from a suitable source table, it's being generated from the minimum and maximum hobby's that do have players.  That doesn't make logical sense and is why people are saying you need to fix your data model.

                    • 37. Re: How to divide a row in a table
                      Lothar Flatz

                      The native Language of the TO is likely German. Can anybody tell me if it is impolite to write a German answer? It seems his English is not good enough to understand the subtle aspects of the issue.

                      • 38. Re: How to divide a row in a table
                        KayK

                        Hallo Lothar,

                        wenn es hilft sein Problem zu lösen, dann antworte ihm einfach in Deutsch. Am Ende eine kleine Zusammenfassung in Englisch, damit wir keinen abhängen.

                        Viele Grüße
                        Kay

                         

                        translation: if it helps the original poster then please answer in german language with a short translation for all the others.

                        • 39. Re: How to divide a row in a table
                          acd6a2be-efda-4b11-b98e-80ece826ad23

                          BlueShadow you are my MEN OF THE DAY many many thanks for this GREAT SOLUTION.

                           

                          Now my Select is COMPLETED

                          • 40. Re: How to divide a row in a table
                            Lothar Flatz

                            No need anymore I would say. Although it seems he still does not understand the issues.

                            • 41. Re: How to divide a row in a table
                              KayK

                              but he has got his output now ...

                              • 42. Re: How to divide a row in a table
                                BluShadow

                                Used exactly the same principles as shown in the first answers too.  *sigh*

                                • 43. Re: How to divide a row in a table
                                  Martin Preiss

                                  maybe language was not the main problem...

                                  • 44. Re: How to divide a row in a table
                                    Lothar Flatz

                                    Certainly not. Was part of it though.

                                    1 2 3 Previous Next