Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

Query problem

I'm tired and can't think this through right now.

I want to Count how many ProductID's are in the Product table that
have a CategoryID in the Category table that has the DepartmentID 56

Product table:
ProductID CategoryID

Category table:
CategoryID DepartmentID Name

I want it to look like this:

CategoryID      Name         Count
244            Medals     30
324            Crosses    12
....

SELECT Product.CategoryID, Category.Name
FROM Product INNER JOIN Category ON Product.CategoryID = Category.CategoryID
WHERE Category.DepartmentID = 56
0
MikeMCSD
Asked:
MikeMCSD
2 Solutions
 
mbizupCommented:
Try this:

SELECT Product.CategoryID, Category.Name, Count(*)
FROM Product INNER JOIN Category ON Product.CategoryID = Category.CategoryID
WHERE Category.DepartmentID = 56
Group BY CategoryID, Category.Name
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

SELECT p.CategoryID, c.Name, COUNT(*)
FROM Product p
INNER JOIN Category c
  ON p.CategoryID = c.CategoryID
  AND c.DepartmentID = 56
GROUP BY p.CategoryID, c.Name
0
 
MikeMCSDAuthor Commented:
thanks guys, I wasn't sure if I should use a subquery or
the group by.
0
 
mcp111Commented:
SELECT P.CategoryID, C.Name, Count(*) as count
FROM Product p JOIN Category c ON P.CategoryID = C.CategoryID
and C.DepartmentID = 56
Group BY p.CategoryID, c.Category.Name
order BY p.CategoryID, c.Category.Name
0
 
mbizupCommented:
Glad to help!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now