CODEWITHSUNDEEP
sqlcountgroup-by
Chris V.
Chris V.

Reputation: 1143

Select count of column grouped by another column SQL

I have two tables, one called Products and one called Products_Category. I want to show the inventory count for each category (grouped by category), and also display the name of the category. The Products table has the inventory count for each Product, but the Products_Category table has the Category names. How can I display the two together? Should I be using some kind of join?

Here's what I'm trying to get:

Category_Name --------- Sum(Products_Inventory)

------Books -------------------- 1 ----------
------Toys---------------------- 2 ----------

But as of right now all I'm able to display is the category ID instead of the category name, like so:

------- 1 ------------------------ 1 ----------
------- 2 ------------------------ 2 ----------

Upvotes: 2

Views: 2735

Answers (3)

rohit singh
rohit singh

Reputation: 11

the most simplest way is to do this..

SELECT a.Category_Name, Sum(b.Products_Inventory)
FROM Products_Category a, Products b
Where a.ProductId = b.ProductId
GROUP BY a.Category_Name

hope it helps

Upvotes: 0

Jeromy French
Jeromy French

Reputation: 12121

For these queries I assume product_id is on both tables (you'll have to use the correct field name if I guessed wrong). I'm also using Product_Category.description as the name of the field that has the category names.

This query will get you all categories, and a count of 0 for any product category that's not found in the Product table.

If Product contains one record for each product in stock (ie, 4 books would mean 4 Product records), than COUNT() is the function you want.

SELECT Products_Category.description, COUNT(Product.product_id) AS category_count
FROM Products_Category
   LEFT OUTER JOIN Products ON Products_Category.product_id=Products.product_id
GROUP BY Products_Category.description

If Product contains one record for each type product in stock (ie, 4 books would mean 1 record with a product_count of 4), than SUM() is the function you want.

SELECT Products_Category.description, SUM(Product.product_count) AS category_count
FROM Products_Category
   LEFT OUTER JOIN Products ON Products_Category.product_id=Products.product_id
GROUP BY Products_Category.description

If you only want records returned when you have inventory in that category, change the LEFT OUTER JOIN to INNER JOIN:

SELECT Products_Category.description, SUM(Product.product_count) AS category_count
FROM Products_Category
   INNER JOIN Products ON Products_Category.product_id=Products.product_id
GROUP BY Products_Category.description

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Use JOIN and GROUP BY:

SELECT PC.Category_Name, Sum(P.Products_Inventory)
FROM Products_Category PC
   INNER JOIN Products P ON PC.ProductId = P.ProductId
GROUP BY PC.Category_Name

BTW -- This assumes you have a ProductId field in each table. And if I could make a suggestion, you should probably have a Category table as well (better for normalization). Store your ProductId and CateogryId in your Products_Category table.

Good luck.

Upvotes: 1

Related Questions