DrMoney DrMoney - 2 months ago 39
SQL Question

SQL: Repeating data in JOIN

I'm doing homework for my class and I can't figure out how to properly answer this question:

"Determine which books generate less than a 55% profit and how many copies of these books have been sold. Summarize your findings for management, and include a copy of the query used to retrieve data from the database tables."

I tried taking a shot at it but I can't seem to get it to come out the way I want it to. It always has data that doesn't seem to go together. Below is my code:

SELECT isbn, b.title, b.cost, b.retail, o.quantity "# of times Ordered",
ROUND(((retail-cost)/retail)*100,1)||'%' "Percent Profit",
o.quantity "# of times Ordered"
FROM books o JOIN orderitems o USING(isbn);


It works in the sense that I get the data I need but it comes up like this:

enter image description here

I have a theory that because the table "Order Items" has multiple orders with the same isbn and different quantities it's selecting all of them. Is there a way to combine them? If not could anyone help me get rid of the redundant data caused by the JOIN?

Thank you!

Answer

I've had to do similar things in SQL Server / MySQL. You need to group by the columns in which you see repeated data that you do not care about, and you need to SUM the field whose values are important to you, probably something like this...

SELECT isbn, b.title, b.cost, b.retail, o.quantity "# of times Ordered",
   ROUND(((retail-cost)/retail)*100,1)||'%' "Percent Profit", 
   SUM(o.quantity) "# of times Ordered"
FROM books o JOIN orderitems o USING(isbn)
GROUP BY isbn, b.title, b.cost, b.retail;

If you need more information, go here and search for SUM: https://docs.oracle.com/javadb/10.6.1.0/ref/rrefsqlj32654.html

Comments