asked on

SQL Server Query (Count and Sum)

I have a SQL Server database query that returns details for a single "BAR_NUMBER".

SET NOCOUNT ON
DECLARE @DBName sysname
DECLARE @MetaID nvarchar(30)
DECLARE @SQL nvarchar(MAX)
SELECT @MetaID = CONVERT(nvarchar, MetaInspectionID)
FROM vc_Inspections WHERE InspectionName = '860097-05'
SET @SQL = '
SET NOCOUNT ON
SELECT
''BAR_NUMBER'',
''DEFECT_SEQUENCE_NUMBER'',
''BAR_GRADE'',
''WIDTH'',
''GAUGE'',
''LENGTH'',
''COGNEX_START_DATE_TIME'',
''RECIPE_NAME'',
''DEFECT_TOP_BOTTOM_CODE'',
''DEFECT_CLASS_ID'',
''DEFECT_CLASS_NAME'',
''DEFECT_SEVERITY'',
''DEFECT_BEGIN_LOCATION_FROM_HEAD'',
''DEFECT_END_LOCATION_FROM_HEAD'',
''DEFECT_DISTANCE_FROM_LEFT_EDGE'',
''DEFECT_DISTANCE_FROM_RIGHT_EDGE'',
''DEFECT_DISTANCE_FROM_CENTER'',
''DEFECT_HEIGHT'',
''DEFECT_WIDTH'',
''DEFECT_AREA''
UNION ALL
SELECT
CONVERT(VARCHAR(16), i.inspectionname) as ''BAR_NUMBER'',
CONVERT(VARCHAR(16), d.defectnumber) as ''DEFECT_SEQUENCE_NUMBER'',
CONVERT(VARCHAR(16), pBG.fieldvalue) as ''BAR_GRADE'',
CONVERT(VARCHAR(16), pW.fieldvalue) as ''WIDTH'',
CONVERT(VARCHAR(16), pGG.fieldValue) as ''GAUGE'',
CONVERT(VARCHAR(16), (i.length / 304.8 )) as ''LENGTH'',
CONVERT(VARCHAR(23), replace(CONVERT(VARCHAR(11), i.starttimelocal, 113),'' '',''-'')
+ '' '' + right(CONVERT(VARCHAR(20), i.starttimelocal, 113),8) + ''.00'') as ''COGNEXT_START_DATE_TIME'',
CONVERT(VARCHAR(16), i.recipename) as ''RECIPE_NAME'',
CONVERT(VARCHAR( 1), d.viewname) as ''DEFECT_TOP_BOTTOM_CODE'',
CONVERT(VARCHAR(16), d.classid) as ''DEFECT_CLASS_ID'',
CONVERT(CHAR(60), dd.class) as ''DEFECT_CLASS_NAME'',
CONVERT(VARCHAR(16), d.severity) as ''DEFECT_SEVERITY'',
CONVERT(VARCHAR(16), (d.boxtop / 304.8 )) as ''DEFECT_BEGIN_LOCATION_FROM_HEAD'',
CONVERT(VARCHAR(16), (d.boxbottom / 304.8 )) as ''DEFECT_END_LOCATION_FROM_HEAD'',
CONVERT(VARCHAR(16), (dd.distanceleftedge / 25.4 )) as ''DEFECT_DISTANCE_FROM_LEFT_EDGE'',
CONVERT(VARCHAR(16), (dd.distancerightedge / 25.4 )) as ''DEFECT_DISTANCE_FROM_RIGHT_EDGE'',
CONVERT(VARCHAR(16), (dd.distancecenter / 25.4 )) as ''DEFECT_DISTANCE_FROM_CENTER'',
CONVERT(VARCHAR(16), (dd.height / 25.4 )) as ''DEFECT_HEIGHT'',
CONVERT(VARCHAR(16), (dd.width / 25.4 )) as ''DEFECT_WIDTH'',
CONVERT(VARCHAR(16), (dd.defectarea / 645.16 )) as ''DEFECT_AREA''
FROM COGNEXDB.dbo.vc_inspections i
JOIN COGNEXDB.dbo.vc_Defects d
ON (i.MetaInspectionID = ' + @MetaID + ') AND (d.MetaInspectionID = i.MetaInspectionID)
JOIN COGNEXDB.dbo.vc_DefectDetails dd
ON (dd.MetaInspectionID = ' + @MetaID + ') AND (dd.DefectID = d.DefectID)
JOIN COGNEXDB.dbo.vc_processinfo pBG
ON (pBG.MetaInspectionID = ' + @MetaID + ') AND (pBG.label = ''Grade'')
JOIN COGNEXDB.dbo.vc_processinfo pGG
ON (pGG.MetaInspectionID = ' + @MetaID + ') AND (pGG.label = ''Gauge'')
JOIN COGNEXDB.dbo.vc_processinfo pW
ON (pW.MetaInspectionID = ' + @MetaID + ') AND (pW.label = ''Width'')'
EXEC (@SQL);

I have attached the example results (data.csv).

I now need to provide a summary based on
DEFECT_CLASS_NAME, DEFECT_TOP_BOTTOM_CODE, COUNT(DEFECT_CLASS_NAME), and SUM(DEFECT_AREA)
so the data looks like this:

860097-05, Dark Defect, T, 222, 10.45
860097-05, Dark Defect, B, 111, 5.33

Basically, for every defect, we need to know the top count, top defect area, bottom count, and bottom defect area.

Can you help me update the query to return the results I desire?

Thanks,

Kevin

viralypatel

insert the result of this query in a # table and write your summary query on that

awking00

A couple of questions. Where is the data.csv file? What is the datatype of defect_area? If sum is needed, why are you converting defect_area to varchar in the union all portion of your query?

sainiak

ASKER

Viralypatel - Can you walk me through a simple example to insert in a # table?

awking00 - Sorry, I selected the file but forgot to click "Attach" - The CSV file should be there now. defect_area is datatype real. You can ignore the varchar conversion - we do that because there is another program that reads the results and it must be text. If I have to do that as a seperate step that is acceptable.
data.csv

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

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

Start Free Trial

sainiak

ASKER

When I run the simplified version, I get this error when I click the "Parse" button:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'SELECT
DEFECT_CLASS_NAME
, DEFECT_TOP_BOTTOM_CODE
, COUNT(DEFECT_CLASS_NAME)
, SUM(DEFECT_AREA)
FROM ('.

I stripped out the SET @SQL = and the EXEC(@SQL) and all the single quotes that were required to see if that helped. It then parsed correctly but got another error on AS DERIVED.

To make it work, I also had to remove the UNION statement.

'SELECT
''DEFECT_TOP_BOTTOM_CODE'',
''DEFECT_CLASS_NAME'',
''DEFECT_AREA''
UNION ALL

I know my original query was complex. Thanks for pointing me in the right direction and helping me find a solution!