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

        Still not clear.

        How does the hobby table have a relationship to the players?  Does it contain a player foreign key?

         

        How difficult is it for you to create some example tables and example date to illustrate your issue?

         

        See, I can create some example data easily enough...

         

        SQL> ed
        Wrote file afiedt.buf

          1  with player(player_number, player_name) as (
          2               select 2, 'Fred' from dual union all
          3               select 4, 'Jim' from dual union all
          4               select 5, 'Bob' from dual union all
          5               select 6, 'John' from dual
          6              )
          7      ,player_hobby(player_number, hobby_id) as (
          8               select 2, 1 from dual union all
          9               select 2, 2 from dual union all
        10               select 4, 2 from dual union all
        11               select 4, 5 from dual union all
        12               select 4, 6 from dual union all
        13               select 5, 1 from dual union all
        14               select 6, 4 from dual union all
        15               select 6, 5 from dual
        16              )
        17      ,hobby(hobby_id, hobby_name) as (
        18               select 1, 'Model Aeroplanes' from dual union all
        19               select 2, 'Radio Controlled Cars' from dual union all
        20               select 3, 'Computer Gaming' from dual union all
        21               select 4, 'Card Games' from dual union all
        22               select 5, 'Tennis' from dual union all
        23               select 6, 'Football' from dual
        24              )
        25  --
        26  -- end of test data
        27  --
        28  select p.player_number
        29        ,p.player_name
        30        ,h.hobby_id
        31        ,h.hobby_name
        32  from   player p
        33         join player_hobby ph on (ph.player_number = p.player_number)
        34*        join hobby h on (h.hobby_id = ph.hobby_id)
        SQL> /

         

        PLAYER_NUMBER PLAY   HOBBY_ID HOBBY_NAME
        ------------- ---- ---------- ---------------------
                    5 Bob           1 Model Aeroplanes
                    2 Fred          1 Model Aeroplanes
                    4 Jim           2 Radio Controlled Cars
                    2 Fred          2 Radio Controlled Cars
                    6 John          4 Card Games
                    6 John          5 Tennis
                    4 Jim           5 Tennis
                    4 Jim           6 Football

         

        8 rows selected.

         

        ... and it's clear what the relationships are of those.

         

        So, that probably doesn't match your tables or data, but at least you can put some effort in to demonstrate the sort of thing you do have in a similar way (use create table and insert statements if you prefer).  It's not difficult to provide a small set of example data so people can see what you're trying to achieve.

         

        Just posting a single column out of two tables that shows no idea of what the relationship is, or what you're trying to get out of it based on that example data, is still wasting our time.

         

        As for...

         

        when i execute the select i can wait many many hours until its done

         

        which select do you mean?

         

        You seem to be expecting help for something without telling people what the actual issue is.  That doesn't work in software development, either on these forums or within your own business.

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

          Okay i will try it again. Sorry for my bad english

           

          Column: hobbynr

          Column: playernr


          SELECT

           

          select h.hobbynr, count(h.playernr)

          from hobbytab h

          where h.jahr = 2014

          group by h.hobbynr

          order by h.playernr

          OUTPUT

           

          hobbynr          playernr

          1                              7

          2                              2

          4                              2

          But the RESULT SHOULD LOOK LIKE THIS

           

          hobbynr          playernr

          1                              7

          2                              2

          3                              0

          4                              2

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

            Hi,
            you need a left join to catch the hobbies without players.
            I modified the example from BluShadow to illustrate

            with player(player_number, player_name) as (
                           select 2, 'Fred' from dual union all
                           select 4, 'Jim' from dual union all
                           select 5, 'Bob' from dual union all
                           select 6, 'John' from dual
                          )
                  ,player_hobby(player_number, hobby_id) as (
                          select 2, 1 from dual union all
                          select 2, 2 from dual union all
                          select 4, 2 from dual union all
                          select 4, 5 from dual union all
                          select 4, 6 from dual union all
                          select 5, 1 from dual union all
            --              select 6, 4 from dual union all -- comment out hobby #4
                          select 6, 5 from dual
                         )
                 ,hobby(hobby_id, hobby_name) as (
                          select 1, 'Model Aeroplanes' from dual union all
                          select 2, 'Radio Controlled Cars' from dual union all
                          select 3, 'Computer Gaming' from dual union all
                          select 4, 'Card Games' from dual union all
                          select 5, 'Tennis' from dual union all
                          select 6, 'Football' from dual
                         )
            --
            -- end of test data
            --
            select h.hobby_id
                   ,count(ph.player_number) as nr_of_players
            from   hobby h
                    left join player_hobby ph on (h.hobby_id = ph.hobby_id)
              group by h.hobby_id
              order by h.hobby_id;
            
            
            --
              HOBBY_ID NR_OF_PLAYERS
            ---------- -----------------------
                     1                       2
                     2                       2
                     3                       0
                     4                       0
                     5                       2
                     6                       1
            
            

             

            regards
            Kay

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

              In this example there are two tables, but i only need the table hobbytab and only this both columns hobbynr, playernr

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

                ok, but in this table you have only hobby 1, 2, 4
                How does your query can know that in the "real world" there is a hobby 3 ? Somewhere in your data model you must find this information.

                Or can it be that there is a hobby 4711 or "11b" ?

                 

                You can do join to a recursive query that produces a bunch of numbers between the minimum and maximum of your hobbies in hobbytab. But are you sure there isn't a hobby 5 ?

                Or you count the hobby numbers from 1 to 2^32 ...

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

                  acd6a2be-efda-4b11-b98e-80ece826ad23 wrote:

                   

                  In this example there are two tables, but i only need the table hobbytab and only this both columns hobbynr, playernr

                   

                  Then why haven't you provided us with some example data to use like you've been asked many times now.

                   

                  Clearly you cannot be bothered.  Likewise I can no longer be bothered to help, and I would guess that is also why so few other people are helping, because you have shown that you can't even supply the information/data that was asked for.

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

                    This Table has hobbys from 1 - 4 but in number 3 there is no player. But i want a output which is look like

                    this

                    hobbynr          playernr

                    1                              7

                    2                              2

                    3                              0

                    4                              2

                     

                    how can i do this ?

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

                      Sorry, that looks like a training question from a sql basis course and it seems to me you do not understand the question.

                       

                      In a correct design you need three tables:

                       

                      Hobby

                      id        name

                      1          Golf

                      2          Tennis

                      3          Chess

                      4          Football

                       

                      Person

                      1           Max

                      2           Carol

                      3           Susan

                       

                       

                      Player

                      Hobby_id   Person_id

                      1                 1

                      2                 1

                      3                 2

                      2                 2

                       

                      Now you can do sql

                      E.g.: How many play each Hobby? To find out what hobbies exist you need to join in the Hobby Table in an outer Join.

                      It is imposible to do your query with one table provided we have a normalized design.

                       

                      select hobby_id, count(*)

                         from player p, hobby H

                      where p.hobby_id(+)  = h.id

                      group by hobby_id;

                      • 23. Re: How to divide a row in a table
                        brunovroman

                        Hello,

                         

                        trying to understand I am puzzled by your two tables... Don't you need a third table?

                        What about this:

                         

                        CREATE TABLE hobby( id NUMBER PRIMARY KEY, txt VARCHAR2(20 CHAR) );

                         

                        CREATE TABLE player( id NUMBER PRIMARY KEY, name VARCHAR2(20 CHAR) );

                         

                        CREATE TABLE who_what( pid NUMBER, hid NUMBER );

                        ALTER TABLE who_what ADD CONSTRAINT who_what_player FOREIGN KEY ( pid ) REFERENCES player( id );

                        ALTER TABLE who_what ADD CONSTRAINT who_what_hobby FOREIGN KEY ( hid ) REFERENCES hobby( id );

                        ALTER TABLE who_what ADD CONSTRAINT who_what_pk PRIMARY KEY ( pid, hid );

                         

                        /* hobby 10 is Golf... */

                        INSERT INTO hobby VALUES ( 10, 'Golf' );

                        INSERT INTO hobby VALUES ( 20, 'Piano' );

                        INSERT INTO hobby VALUES ( 30, 'Soccer' );

                        INSERT INTO hobby VALUES ( 40, 'Tennis' );

                        INSERT INTO hobby VALUES ( 50, 'Fairy Chess' );

                         

                        /* player 100 is Sarah... */

                        INSERT INTO player VALUES ( 100, 'Sarah' );

                        INSERT INTO player VALUES ( 200, 'John' );

                        INSERT INTO player VALUES ( 300, 'Andrea' );

                        INSERT INTO player VALUES ( 400, 'Kim' );

                        INSERT INTO player VALUES ( 500, 'Bruno' );

                         

                        /* player 100 has hobby 10... */

                        INSERT INTO who_what VALUES ( 100, 10 );

                        INSERT INTO who_what VALUES ( 100, 20 );

                        INSERT INTO who_what VALUES ( 100, 50 );

                        INSERT INTO who_what VALUES ( 200, 20 );

                        INSERT INTO who_what VALUES ( 300, 10 );

                        INSERT INTO who_what VALUES ( 500, 50 );

                         

                        COMMIT;

                         

                        REM "Question 1": how many players have one hobby, how many have two hobbies, three, ...

                         

                        WITH hobbies_per_player AS

                        ( SELECT COUNT(*) nb

                            FROM who_what

                            GROUP BY pid

                        )

                        , one_to_nbmax AS

                        ( SELECT LEVEL x

                            FROM dual

                            CONNECT BY LEVEL <= ( SELECT MAX( nb )

                                                    FROM hobbies_per_player

                                                )

                        )

                        SELECT x.x nb_hobbies, COUNT( y.nb) nb_players

                          FROM one_to_nbmax x

                          LEFT OUTER JOIN hobbies_per_player y

                          ON x.x = y.nb

                          GROUP BY x.x

                          ORDER BY x.x

                        ;

                        NB_HOBBIES NB_PLAYERS

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

                                 1          3

                                 2          0

                                 3          1

                        (so: 3 players have a single hobby (200, 300 and 500), no players have 2 hobbies, 1 player has 3 hobbies (100))

                         

                         

                        REM "Question 2": how many hobbies are chosen by 1 player, byt 2 players, ...?

                        WITH players_per_hobby AS

                        ( SELECT COUNT(*) nb

                            FROM who_what

                            GROUP BY hid

                        )

                        , one_to_nbmax AS

                        ( SELECT LEVEL x

                            FROM dual

                            CONNECT BY LEVEL <= ( SELECT MAX( nb )

                                                    FROM players_per_hobby

                                                )

                        )

                        SELECT x.x nb_players, COUNT( y.nb) nb_hobbies

                          FROM one_to_nbmax x

                          LEFT OUTER JOIN players_per_hobby y

                          ON x.x = y.nb

                          GROUP BY x.x

                          ORDER BY x.x

                        ;

                        NB_PLAYERS NB_HOBBIES

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

                                 1          0

                                 2          3

                        (so: no hobby has one player and 2 hobbies have three players (10, 20, 50))

                         

                        A bit more complex would be to query also with the "zeroes" if we want to include the fact that a player (400) has zero hobby in question 1, or the fact that two hobbies (30 and 40) have zero players in question 2, but I think that what I've written is enough in a first time.

                         

                        Best regards,

                         

                        Bruno Vroman.

                        Edited: sorry, I replied to entry 14 thinking it was the last one but it was just the last one of page 1... I see that what I've suggested is similar to entry 22 of Lothar.

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

                          And how can i split a column number field.

                           

                          Example:  Hobbynr  : 5

                           

                          Select ????

                           

                          Output

                           

                          Hobbynr

                           

                          1

                          2

                          3

                          4

                          5

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

                            acd6a2be-efda-4b11-b98e-80ece826ad23 wrote:

                             

                            Output

                             

                            Hobbynr

                             

                            1

                            2

                            3

                            4

                            5

                             

                            select level "Hobbynr" from dual connect by level <= 5;

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

                              finally it seems to me you want the association table generated. The following sql assigns the first hobbies according to a counter.

                              It does not make much sense, but it can generate test data for further queries:

                               

                              with player(player_number, player_name, hobbies) as (

                                            select 2, 'Fred', 1 from dual union all

                                             select 4, 'Jim',3 from dual union all

                                             select 5, 'Bob',2 from dual union all

                                             select 6, 'John',2 from dual

                                             )

                                    ,hobby(hobby_id, hobby_name) as (

                                             select 1, 'Model Aeroplanes' from dual union all

                                             select 2, 'Radio Controlled Cars' from dual union all

                                             select 3, 'Computer Gaming' from dual union all

                                             select 4, 'Card Games' from dual union all

                                             select 5, 'Tennis' from dual union all

                                             select 6, 'Football' from dual

                                            )

                              --

                              -- end of test data

                              --

                              select p.player_number

                                     ,h.hobby_id

                              from   player p,

                                       hobby h

                              Where hobby_id <= hobbies

                              ;

                               

                              PLAYER_NUMBER   HOBBY_ID

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

                                          4                     3

                                          4                     2

                                          4                     1

                                          6                     2

                                          6                     1

                                          5                     2

                                          5                     1

                                          2                     1

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

                                Thats not correct Kayk

                                 

                                you have a constant number 5 in your where clausel. But i want a select which only gives out what stands in the Column

                                 

                                for Example

                                 

                                select h.hobbynr

                                from hobbytab h

                                where h.year = 2014

                                 

                                output

                                 

                                Hobbynr

                                 

                                10

                                 

                                select h.hobbynr

                                from hobbytab h

                                where h.year = 2013

                                 

                                 

                                output

                                 

                                Hobbynr

                                 

                                15

                                 

                                And i want this

                                 

                                Hobbynr

                                1

                                2

                                3

                                ..

                                ...

                                ...

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

                                  acd6a2be-efda-4b11-b98e-80ece826ad23 wrote:

                                   

                                  And how can i split a column number field.

                                   

                                  Example:  Hobbynr  : 5

                                   

                                  Select ????

                                   

                                  Output

                                   

                                  Hobbynr

                                   

                                  1

                                  2

                                  3

                                  4

                                  5

                                   

                                  You were shown this last week by myself.

                                   

                                  Clearly you are not reading or understanding the answers you are being given.

                                  Clearly you are putting no effort into applying these techniques to your own data... .that very same data you STILL have failed to provide.

                                   

                                  WHY ARE YOU INCAPABLE OF PROVIDING SOME CREATE TABLE/INSERT STATEMENTS THAT DEMONSTRATE YOUR TABLES WITH SOME SUITABLE EXAMPLE DATA.

                                   

                                  You aren't worth people wasting their time and effort trying to help.  This thread is nearly at 30 posts and you still haven't provided the information asked for.

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

                                    i know im a hard case, but i want this **** select

                                     

                                    I  want a select on TWO COLUMNS on ONE TABLE

                                     

                                    Column One:  Hobbynr  <-- in this colum there are the numbers of the hobbys    1: Tennis 2: Soccer 3: Gold etc NO RELATION TO OTHER TABLES

                                    Column Two: Personnr <-- this column include the number of persons THERE ARE NO RELATIONS BETWEEN A OTHER TABLE LIKE PERSON_TAB OR SOMETHING ELSE

                                     

                                    my current select looks like this

                                     

                                    select h.hobbynr, count(h.personnr)

                                    from hobbytab h

                                    where h.year = 2014

                                    group by h.hobbynr

                                    order by h.hobbynr

                                    the select shows me

                                    hobbynr      personnr

                                    1(Tennis)       5( 5 Persons who have the hobby Tennis)

                                    2                      4

                                    3                      8

                                    5                      2

                                    He doesent show me the number 4 of the hobbynr because there are no person BUT I WANT

                                     

                                    hobbynr      personnr

                                    1(Tennis)       5( 5 Persons who have the hobby Tennis)

                                    2                      4

                                    3                      8

                                    4                      0

                                    5                      2