Need help with SQL query for book sales and inventory
I’m working on a database for a bookstore and I’m stuck. I want to figure out which publisher has generated the most revenue by combining the cost of the books we’ve sold with the value of the current stock for each publisher.
Below is an overview of our database structure:
books: book_id, title, author_id, subject_id
publishers: publisher_id, name, address
authors: author_id, last_name, first_name
stock: isbn, cost, retail_price, stock
shipments: shipment_id, customer_id, isbn, ship_date
customers: customer_id, last_name, first_name
editions: isbn, book_id, edition, publisher_id, publication_date
subjects: subject_id, subject, location
I tried running a couple of queries separately:
- One for the shipped books
- Another for the current stock
However, I ended up manually adding the results. I’m wondering if it’s possible to merge these into a single query. I assume that using JOIN and SUM might work, but my attempts have led to errors.
Any help on this would be greatly appreciated. Thanks!
yo maya, i’ve dealt with similar stuff before. try this:
SELECT p.name, SUM(s.retail_price * (sh.quantity + s.stock)) as total_value
FROM publishers p
JOIN editions e ON p.publisher_id = e.publisher_id
JOIN stock s ON e.isbn = s.isbn
LEFT JOIN shipments sh ON s.isbn = sh.isbn
GROUP BY p.publisher_id, p.name
ORDER BY total_value DESC;
this should give u what u need in 1 query. lmk if u need more help!
Combining inventory costs and sales data in a single SQL query is definitely possible. One approach is to first calculate the revenue from the books that have been sold by joining the shipments, editions, and stock tables. Next, calculate the current inventory value by multiplying the remaining stock with the retail price. Finally, join these aggregated results with the publishers table and group by publisher.
For example:
SELECT p.name AS publisher,
SUM(COALESCE(sold.revenue, 0) + COALESCE(inventory.value, 0)) AS total_value
FROM publishers p
LEFT JOIN (
SELECT e.publisher_id,
SUM(s.retail_price) AS revenue
FROM shipments sh
JOIN editions e ON sh.isbn = e.isbn
JOIN stock s ON e.isbn = s.isbn
GROUP BY e.publisher_id
) AS sold ON p.publisher_id = sold.publisher_id
LEFT JOIN (
SELECT e.publisher_id,
SUM(s.stock * s.retail_price) AS value
FROM editions e
JOIN stock s ON e.isbn = s.isbn
GROUP BY e.publisher_id
) AS inventory ON p.publisher_id = inventory.publisher_id
GROUP BY p.publisher_id, p.name
ORDER BY total_value DESC;
This query should provide a comprehensive overview of each publisher’s total value by merging sold items and current stock into one combined result.
hey maya! have u considered using a CTE (Common Table Expression) for this? it might make ur query easier to read and maintain. something like:
WITH sales AS (
SELECT e.publisher_id, SUM(s.retail_price) AS revenue
FROM shipments sh
JOIN editions e ON sh.isbn = e.isbn
JOIN stock s ON e.isbn = s.isbn
GROUP BY e.publisher_id
)
-- rest of the query here
what do u think? wanna give it a shot?