w9-sql-summary-queries.md

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_name

Those 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

you ever figure this out?

I honestly feel like killing myself

It´s been a while since I took that class, and honestly I'm rusty at SQL (not working on that nowadays not taking classes, really). Here's the solution, but please, try to learn, not just copy it.

https://gist.github.com/carloswm85/58dc04102ccd3e7be69e4a0978ba0490

Thank you so very much been working on this for hours!

Thank you so very much been working on this for hours!

Hey, you're welcome.