asked on

MS SQL - JOIN GROUP with Count/SUM

I posted this a day ago at
https://www.experts-exchange.com/questions/25098572/SQL-Join-Group-and-SUM.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.

Anthony Perkins

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

Anthony Perkins

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 Jones

ASKER

Thanks.. I get

Invalid column name 'CategoryID'. (this is in the Product_Table and is valid) if that helps you

Kristen Jones

ASKER

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"

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kristen Jones

ASKER

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 Jones

ASKER

.. wait. it may be ok.. found a small error in my data...

Kristen Jones

ASKER

WORKS thanks