Link to home
Create AccountLog in
Avatar of paiman01
paiman01

asked on

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

Avatar of Paulo Pimenta
Paulo Pimenta
Flag of Portugal image

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

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

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.
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Great Thank you, this worked exactly as i wanted