7 Replies Latest reply: Jul 31, 2013 6:54 PM by chris227 RSS

    Calculating percentages from two tables

    a35c130d-ca92-46ca-8177-8a8b5ee02876

      Hello,

       

      I'm been racking my brain over this, but have been unable to to get it to work. Basically, I need to find the 5 most frequently purchased books and the percentage of profit each book generates. To do this, I know I need to join two tables ('orderitems' and 'books'), via ISBN, to get this information.... but being new to SQL, I have been failing miserably.

       

      I am using Oracle Database 11g Express. I will post the code to create / populate the tables in a follow-up post.

       

      Ideally, I'd like my output to look like this (below).

       

            RANK TITLE                                             % of PROFIT

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

               1    HOW TO MANAGE THE MANAGER   88.00%

               2    PAINLESS CHILD-REARING              87.00%

               3    SHORTEST POEMS                          82.83%

               4    DATABASE IMPLEMENTATION        78.18%

               5    BIG BEAR AND LITTLE DOVE           68.23%

       

       

      So far, I have the below code. I know this is purely ranking purely on profit of the numbers within the 'books' table, but I know this should help get you guys into the ballpark.

       

      SELECT rownum AS Rank, title, profit

      FROM (SELECT title, ((retail – cost) / cost * 100) profit

            FROM books

            ORDER BY profit DESC)

      WHERE rownum < 6;

       

      Any feedback / help would be greatly appreciated! Thank you.

        • 1. Re: Calculating percentages from two tables
          a35c130d-ca92-46ca-8177-8a8b5ee02876

          Code to create the two identified tables:

           

          CREATE TABLE Books

          (ISBN     VARCHAR2(10) PRIMARY KEY,

          Title    VARCHAR2(30),

          PubDate  DATE,

          PubID    NUMBER (2),

          Cost     NUMBER (5,2),

          Retail   NUMBER (5,2),

          Discount NUMBER (4,2),

          Category VARCHAR2(12) );

           

          CREATE TABLE ORDERITEMS

          (Order# NUMBER(4),

          Item# NUMBER(2),

          ISBN VARCHAR2(10),

          Quantity NUMBER(3),

          PaidEach NUMBER(5,2) );

           

           

           

          INSERT INTO BOOKS

          VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-05',4,18.75,30.95, NULL, 'FITNESS');

          INSERT INTO BOOKS

          VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-05',1,14.20,22.00, NULL, 'FAMILY LIFE');

          INSERT INTO BOOKS

          VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-06',2,37.80,59.95, 3.00, 'CHILDREN');

          INSERT INTO BOOKS

          VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-03',3,31.40,55.95, NULL, 'COMPUTER');

          INSERT INTO BOOKS

          VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-04',4,12.50,19.95, NULL, 'COOKING');

          INSERT INTO BOOKS

          VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-05',3,47.25,75.95, 3.80, 'COMPUTER');

          INSERT INTO BOOKS

          VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-05',3,21.80,25.00, NULL, 'COMPUTER');

          INSERT INTO BOOKS

          VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-06',2,37.90,54.50, NULL, 'COMPUTER');

          INSERT INTO BOOKS

          VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-04',5,48.00,89.95, 4.50, 'FAMILY LIFE');

          INSERT INTO BOOKS

          VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-04',4,19.00,28.75, NULL, 'COOKING');

          INSERT INTO BOOKS

          VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-05',5,5.32,8.95, NULL, 'CHILDREN');

          INSERT INTO BOOKS

          VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-06',4,17.85,29.95, 1.50, 'SELF HELP');

          INSERT INTO BOOKS

          VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-03',1,15.40,31.95, NULL,  'BUSINESS');

          INSERT INTO BOOKS

          VALUES ('2147428890','SHORTEST POEMS','01-MAY-05',5,21.85,39.95, NULL, 'LITERATURE');

           

          INSERT INTO ORDERITEMS VALUES (1000,1,'3437212490',1,19.95); 

          INSERT INTO ORDERITEMS VALUES (1001,1,'9247381001',1,31.95); 

          INSERT INTO ORDERITEMS VALUES (1001,2,'2491748320',1,85.45); 

          INSERT INTO ORDERITEMS VALUES (1002,1,'8843172113',2,55.95); 

          INSERT INTO ORDERITEMS VALUES (1003,1,'8843172113',1,55.95); 

          INSERT INTO ORDERITEMS VALUES (1003,2,'1059831198',1,30.95);

          INSERT INTO ORDERITEMS VALUES (1003,3,'3437212490',1,19.95);

          INSERT INTO ORDERITEMS VALUES (1004,1,'2491748320',2,85.45);

          INSERT INTO ORDERITEMS VALUES (1005,1,'2147428890',1,39.95);

          INSERT INTO ORDERITEMS VALUES (1006,1,'9959789321',1,54.50);

          INSERT INTO ORDERITEMS VALUES (1007,1,'3957136468',3,72.15);

          INSERT INTO ORDERITEMS VALUES (1007,2,'9959789321',1,54.50);

          INSERT INTO ORDERITEMS VALUES (1007,3,'8117949391',1,8.95);

          INSERT INTO ORDERITEMS VALUES (1007,4,'8843172113',1,55.95);

          INSERT INTO ORDERITEMS VALUES (1008,1,'3437212490',2,19.95);

          INSERT INTO ORDERITEMS VALUES (1009,1,'3437212490',1,19.95);

          INSERT INTO ORDERITEMS VALUES (1009,2,'0401140733',1,22.00);

          INSERT INTO ORDERITEMS VALUES (1010,1,'8843172113',1,55.95);

          INSERT INTO ORDERITEMS VALUES (1011,1,'2491748320',1,85.45);

          INSERT INTO ORDERITEMS VALUES (1012,1,'8117949391',1,8.95);

          INSERT INTO ORDERITEMS VALUES (1012,2,'1915762492',2,25.00);

          INSERT INTO ORDERITEMS VALUES (1012,3,'2491748320',1,85.45);

          INSERT INTO ORDERITEMS VALUES (1012,4,'0401140733',1,22.00);

          INSERT INTO ORDERITEMS VALUES (1013,1,'8843172113',1,55.95);

          INSERT INTO ORDERITEMS VALUES (1014,1,'0401140733',2,22.00);

          INSERT INTO ORDERITEMS VALUES (1015,1,'3437212490',1,19.95);

          INSERT INTO ORDERITEMS VALUES (1016,1,'2491748320',1,85.45);

          INSERT INTO ORDERITEMS VALUES (1017,1,'8117949391',2,8.95);

          INSERT INTO ORDERITEMS VALUES (1018,1,'3437212490',1,19.95);

          INSERT INTO ORDERITEMS VALUES (1018,2,'8843172113',1,55.95);

          INSERT INTO ORDERITEMS VALUES (1019,1,'0401140733',1,22.00);

          INSERT INTO ORDERITEMS VALUES (1020,1,'3437212490',1,19.95);

           

           

           

          COMMIT;

          • 2. Re: Calculating percentages from two tables
            Frank Kulash

            Hi,

             

            Thanks for posting the sample data.

            Don't forget to explain, step by step, how you get the results you posted from the given sample data.

             

            Is the "most frequently purchased book" determined by the number of rows in orderitems, or by the quantity column?  Either way, I would think that 'COOKING WITH MUSHROOMS' would be the most frequently purchased book in this sample set.  Why don't you want that title in the results?

             

            What is a "book"?  Is it determined by title or by isbn?  For example, if the same work has hardcover, softcover and e-book editions, all with separate isbns, would you want to count them are 3 separate books?

             

            Which columns play a role in this problem?  For example, does orderitems.paideach matter?

             

            Don't try to insert a VARCHAR2 value, such as '21-JAN-05' into a DATE column.  Use the TO_DATE function to convert a aVARCHAR2 into a DATE.

            • 3. Re: Calculating percentages from two tables
              a35c130d-ca92-46ca-8177-8a8b5ee02876

              Hi Frank,

               

              The "most frequently purchased book" is determined based off the quantity column of the 'orderitems' table.


              A book is determined by each unique ISBN from the 'books' table.

               

              The "most frequently purchased book" is determined by the number of rows in orderitems AND by the quantity column? Meaning, the same book can be purchased more than once (thus, multiple lines) and with multiple quantities. ISBN is the unique identifier for the orderitems table

               

              Orderitems.paid each should be used to calculate the 'percentage of profit' based off the books.cost column.

               

              Please let me know if you need anything further. Thanks for your help!

              • 4. Re: Calculating percentages from two tables
                Frank Kulash

                Hi,

                 

                 

                 

                a35c130d-ca92-46ca-8177-8a8b5ee02876 wrote:

                 

                Hi Frank,

                 

                The "most frequently purchased book" is determined based off the quantity column of the 'orderitems' table.


                A book is determined by each unique ISBN from the 'books' table.

                 

                The "most frequently purchased book" is determined by the number of rows in orderitems AND by the quantity column? Meaning, the same book can be purchased more than once (thus, multiple lines) and with multiple quantities. ISBN is the unique identifier for the orderitems table

                 

                Orderitems.paid each should be used to calculate the 'percentage of profit' based off the books.cost column.

                 

                Please let me know if you need anything further. Thanks for your help!

                Sorry, I still don't understand.

                 

                Is the "most frequently purchased book" determined by quantity, as you said in your 1st paragraph above, or is it determined by both quantity and number of rows, as you say in the 3rd paragraph?  If it's both, explain how both affect the results; that is, take one book, and show, step by step, how you compute how frequently it was purchased.

                In any event, why isn't 'COOKING WITH MUSHROOMS' one of the top 5?

                 

                How is percentage of profit calculated?  Again, pick one book (preferably one of the top 5) and show, step by step, how you calculate its percentage of profit.

                 

                Do all of the columns you posted play some role in this problem?  For example, how does books.pubid affect the results?

                • 5. Re: Calculating percentages from two tables
                  a35c130d-ca92-46ca-8177-8a8b5ee02876

                  To clarify, "most frequently purchased books" should be determined by quantity.

                   

                  For the content I posted, this was just filler... not the exact results. I was purely showing just the formatting. Ignore said content. 'Cooking with mushrooms' should be in the top 5, I believe.

                   

                  How is percentage of profit calculated?  Again, pick one book (preferably one of the top 5) and show, step by step, how you calculate its percentage of profit.


                  Actually, the % of profit should be calculated by using the formula ((retail-cost) / cost * 100) based purely off the 'books' table.

                   

                  Do all of the columns you posted play some role in this problem?  No.

                  • 6. Re: Calculating percentages from two tables
                    Frank Kulash

                    Hi,

                     

                    Post the results you want from the sample data.

                     


                    • 7. Re: Calculating percentages from two tables
                      chris227

                      SELECT

                      title

                      ,((retail – cost) / cost * 100) profit

                      FROM books b

                          ,(select

                              rank() over (order by sum(quantity) desc) rnk

                             ,isbn

                            from orderitems

                            group by isbn

                           ) o

                      where

                      b.isbn = o.isbn

                      and

                      rnk < 6


                      You want to use row_number (similar to rownum) or dense_rank() instead of rank() depending on your requirements.

                      e.g.

                      row_number() over (order by sum(quantity) desc) rnk

                      or

                      dense_rank() over (order by sum(quantity) desc) rnk