Complex SQL Statement

I have the below SQL statement which works. My challenge is that I need to do the same count of the category codes but multiple times for different values. So as an example (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=2)), (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=3)), etc.

Thank you for your help.

SELECT tbl_Unit.Name, tbl_Unit.Location, tbl_Asset_Inspection.NSN, tbl_Asset.NOMENCLATURE, tbl_LocationAssets.CAT_III, tbl_LocationAssets.CAT_IV, tbl_LocationAssets.CAT_II_IV, Count(tbl_Asset_Inspection.CATEGORY_CODE) AS CountOfCATEGORY_CODE
FROM tbl_Asset INNER JOIN ((tbl_LocationAssets INNER JOIN tbl_Asset_Inspection ON tbl_LocationAssets.iAssetID = tbl_Asset_Inspection.iParentID) INNER JOIN tbl_Unit ON tbl_LocationAssets.iParentID = tbl_Unit.iParentID) ON tbl_Asset.ID = tbl_Asset_Inspection.iParentID
GROUP BY tbl_Unit.Name, tbl_Unit.Location, tbl_Asset_Inspection.NSN, tbl_Asset.NOMENCLATURE, tbl_LocationAssets.CAT_III, tbl_LocationAssets.CAT_IV, tbl_LocationAssets.CAT_II_IV
HAVING (((Count(tbl_Asset_Inspection.CATEGORY_CODE))=1))

Open in new window

DatabasesMicrosoft SQL Server 2005SQL

Last Comment

paiman01

8/22/2022 - Mon

Paulo Pimenta

4/13/2008

I believe that if you don't use the HAVING condition you'll achieve your objective.

SELECT 
  tbl_Unit.Name, 
  tbl_Unit.Location, 
  tbl_Asset_Inspection.NSN, 
  tbl_Asset.NOMENCLATURE, 
  tbl_LocationAssets.CAT_III, 
  tbl_LocationAssets.CAT_IV, 
  tbl_LocationAssets.CAT_II_IV, 
  Count(tbl_Asset_Inspection.CATEGORY_CODE) AS CountOfCATEGORY_CODE
FROM tbl_Asset INNER JOIN ((tbl_LocationAssets INNER JOIN tbl_Asset_Inspection ON tbl_LocationAssets.iAssetID = tbl_Asset_Inspection.iParentID) INNER JOIN tbl_Unit ON tbl_LocationAssets.iParentID = tbl_Unit.iParentID) ON tbl_Asset.ID = tbl_Asset_Inspection.iParentID
GROUP BY tbl_Unit.Name, tbl_Unit.Location, tbl_Asset_Inspection.NSN, tbl_Asset.NOMENCLATURE, tbl_LocationAssets.CAT_III, tbl_LocationAssets.CAT_IV, tbl_LocationAssets.CAT_II_IV

Open in new window

Paulo Pimenta

4/13/2008

That way, you obtain all count of tbl_Asset_Inspection.CATEGORY_CODE.

paiman01

4/13/2008

ASKER

Hello,
Thank you for your response. I am not sure if that would work. I need to create a column which contains a count of CATEGORY_CODE based on condition. Then solution would contain 5 columns one for the count on CATEGORY_CODE where the value is equal 1, then second count on CATEGORY_CODE where the value is equal 2, etc. I need this one SQL so that i can take the results into CrystalReports.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

ASKER CERTIFIED SOLUTION

ee_rlee

4/13/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts