Query problem

Posted on 2007-08-10
Last Modified: 2010-03-19
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
Question by:MikeMCSD
    LVL 61

    Accepted Solution

    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
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]

    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
    LVL 16

    Author Comment

    thanks guys, I wasn't sure if I should use a subquery or
    the group by.
    LVL 6

    Expert Comment

    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
    LVL 61

    Expert Comment

    Glad to help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now