https://byui.instructure.com/courses/162912/assignments/7388239?module_item_id=20790988
How can I solve this?
-- 3. How many of each category of bikes do we have in our "Baldwin Bikes" store, which has the store_id of 2.
-- We need to see the name of the category as well as the number of bikes in the category.
-- Sort it by lowest numbers first.
USE bike;
SELECT * FROM category;
SELECT * FROM product;
SELECT * FROM stock;
SELECT * FROM store;
SELECT category_name, p.product_id, quantity, sk.store_id, sum(quantity)
FROM category c
JOIN product p ON c.category_id = p.category_id
JOIN stock sk ON sk.product_id = p.product_id
JOIN store st ON sk.store_id = st.store_id
WHERE sk.store_id = 2 -- filtering
GROUP BY p.category_id;Tables are something like:
category
category_id
category_name
product
product_id
category_id
stock
product_id
store_id
quantity
store
store_id
store_nameThose are the values I'm focusing in.
I've spent like 2 hours trying different things, unsuccessfully.
you ever figure this out?
I know you are done with this class but this is what I got but it doesn't filter it out to the specific store
USE BIKE;
SELECT category_NAME, SUM(QUANTITY) as instock
FROM product p
JOIN category c
ON c.category_id = p.category_id
JOIN STOCK S
ON p.product_id = s.PRODUCT_ID
group by category_name;
I honestly feel like killing myself