SQL Join Group and SUM

Kristen Jones
Kristen Jones used Ask the Experts™
on
Got two tables  Category  and Product_Table  

Category table has the following Fields:

CatID
MainCAT
SubCat

Product_Table has
PN
ClearanceID

The ClearanceID is populated with the CatID from the Category table


What I am trying to do is make a record set that Lists the CategoryID, MainCat and SubCat with a COUNT of how many products in the Product_Table have matching ClearanceID

So the record set would look something like this

CategoryID, MainCat ,SubCat , TheCount

123       Boat       Sail       23
124       Boat       Motor    4
125       Car         Sport    3
126       Car         Van      34

But I do not want any records returned where there are no products from the product table
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT
    CategoryID,
    MainCat,
    SubCat,
    COUNT(*) TheCount
FROM Category c
INNER JOIN Product_Table p
    ON c.CatID = p.ClearanceID
GROUP BY
    CategoryID,
    MainCat,
    SubCat
ORDER BY CategoryID
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try this:

SELECT t1.CategoryID, t1.MainCat ,t1.SubCat , count(t2.ClearanceID) TheCount
FROM Category t1
inner join Product_Table t2 on t1.CatID = t2.ClearanceID
GROUP BY t1.CategoryID, t1.MainCat ,t1.SubCat
Here we go.

SELECT
	CAT.CATID,
	CAT.MAINCAT,
	CAT.SUBCAT,
	COUNT(PT.PN) AS 'TheCount'
FROM 
	CATEGORY AS CAT
INNER JOIN 	Product_Table AS PT ON
	CAT.CatID = PT.ClearanceID
GROUP BY
	CAT.CATID,
	CAT.MAINCAT,
	CAT.SUBCAT

Open in new window

Kristen JonesGIS/APP Dev

Author

Commented:
Thanks Guys that was fast both work great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial