MS SQL - JOIN GROUP with Count/SUM

Kristen Jones
Kristen Jones used Ask the Experts™
on
I posted this a day ago at
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25098572.html

After I started populating data I found that the solutions do not work..

SO.......

Got two tables  Category  and Product_Table  

Category table has the following Fields:

CatID   (number)
MainCAT  (Text)
SubCat  (Text)

Product_Table has
PN  (VNARCHAR)
ClearanceID (Number)
CategoryID (number)


The ClearanceID in the product_table is populated with the CatID from the Category table



What I am trying to do is make a record set that Lists the CategoryID, ClearanceID (or CatID as they are the same) , 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 which can be removed by either a non matching ClearanceID to the Category table, or if the CategoryID is not 45 (45 identifies a clearance item)  or you could if the ClearanceID is null or not 45..  does not matter.  

The last two solutions from the other day, do not count and I think I may have messed up the request.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
SELECT  CategoryID,
        MainCat,
        SubCat,
        ProductCount
FROM    Category c
        INNER JOIN (
                        SELECT      ClearanceID,
                                    COUNT(*) ProductCount
                        FROM      Product_Table
                        WHERE      CategoryID <> 45
                        GROUP BY
                                    ClearanceID) p ON c.CatID = p.ClearanceID
ORDER BY CategoryID
Top Expert 2012

Commented:
I missed the aliases.  Here is the corrected version:
SELECT  c.CategoryID,
        c.MainCat,
        c.SubCat,
        p.ProductCount
FROM    Category c
        INNER JOIN (SELECT  ClearanceID,
                            COUNT(*) ProductCount
                    FROM    Product_Table
                    WHERE   CategoryID <> 45
                    GROUP BY 
							ClearanceID
                   ) p ON c.CatID = p.ClearanceID
ORDER BY 
		c.CategoryID

Open in new window

Kristen JonesGIS/APP Dev

Author

Commented:
Thanks..  I get

Invalid column name 'CategoryID'.  (this is in the Product_Table and is valid) if that helps you
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Kristen JonesGIS/APP Dev

Author

Commented:
Also. CategoryID that is 45 IS what I want, that identifys the item as a clearance, I think I may have mucked that part up in my "problem descrption"
Top Expert 2012
Commented:
Let's try it again:
SELECT  c.CatID, 
        c.MainCat, 
        c.SubCat, 
        p.ProductCount 
FROM    Category c 
        INNER JOIN (SELECT  ClearanceID, 
                            COUNT(*) ProductCount 
                    FROM    Product_Table 
                    WHERE   CategoryID <> 45 
                    GROUP BY  
                                                        ClearanceID 
                   ) p ON c.CatID = p.ClearanceID 
ORDER BY  
                c.CatID

Open in new window

Kristen JonesGIS/APP Dev

Author

Commented:
No Errors this time and I modded the line:

HERE   CategoryID = 45

but I get only two records and the product count is 1 for both..  I currently have about 20 records and various categories (the ClearanceID)  so something is not right still.
Kristen JonesGIS/APP Dev

Author

Commented:
..  wait.  it may be ok..  found a small error in my data...  
Kristen JonesGIS/APP Dev

Author

Commented:
WORKS  thanks

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