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

# Calculating percentages from two tables

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

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

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

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

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

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

Hi,

Post the results you want from the sample data.

• ###### 7. Re: Calculating percentages from two tables

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